Import .csv file to SAS, clean NULLs

1
votes

I'm importing .csv files to SAS by using PROC IMPORT which works fine. However, some of my quantitative columns have NULLs as I generated the .csv files by exporting from SQL. When SAS goes through the columns, he classifies these columns as character even if only 1 of 1000 entries is NULL (which SAS thinks is a string).

Is there any way to tell SAS that NULL represents a missing value and should be taken care of accordingly?

Thanks

2
sqlcsvimportsas

2 Answers

0
votes

Not with PROC IMPORT. You could pre-process the file and convert the string 'NULL' into actual empty space. 

data _null_;
   infile 'my.csv' dsd truncover length=ll column=cc ;
   outfile 'new.csv' dsd ;
   length cell $5000 ;
   do while (cc < ll);
     input cell @;
     if cell='NULL' then cell=' ';
     put cell @;
   end;
   put;
run;

Or you could read the file using a data step and create a user defined informat that converts 'NULL' to missing.

proc format ;
  invalue nulls 'NULL'=. ;
run;
data _null_ ;
   infile 'my.csv' dsd ;
   informat mynumber nulls. ;
   input mynumber ;
run;
0
votes

Thanks for the feedback. I ended up using the following:

data lib.project;
set lib.project;
array change _character_;
do over change;
if change="NULL" then change=.;
end;
run;