Alternative to mixed=yes when using Proc Import on a CSV file

KenD — Wed, 03 Mar 2010 15:09:27 GMT

I've got mixed characters in one of my columns in a CSV file I'm importing. I know that mixed=yes will work when importing an excel sheet, but I get an error when using this with a csv file. Does this feature just not work when using Proc Import on anything besides Excel? If so, what are my alternatives aside from writing a big input statement?

Thanks for any help!

Re: Alternative to mixed=yes when using Proc Import on a CSV file

MikeZdeb — Wed, 03 Mar 2010 15:44:53 GMT

hi ... one idea, you don't have to write a LONG input statement
you just have to write the names of the variables that should be
CHARACTER in a LENGTH statement and the variables that should be
SAS DATES in an INFORMAT statement

this assumes that the first record in your CSV file contains the names of the variables separated by commas

assume this is TEST.CSV ...

name,age,zip,gender,dob
MIKE,63,12203,M,3/7/1947
SARA,32,20001,F,9/24/1976
ARMMONDO,99,12345,U,1/1/1960

* the CSV file;
filename x 'z:\test.csv';

* read 1st record, place variable names in a macro variable;
data _null_;
infile x obs=1;
input;
call symputx('vars',translate(_infile_,' ',','));
run;

*
read the data
add a LENGTH statement for only the character variables
arbitrarily assign a length of 20 to all character data
(changed later in code)
add an INFORMAT and FORMAT for all dates
;
data test;
length name zip gender $20;
informat dob mmddyy10.;

infile x firstobs=2 dsd;
input &vars;

format dob mmddyy10.;
run;

* what happened;
proc contents data=test;
ods select variables;
run;

*
find maximum length of all character variables
create a length statement as a macro variable
;
data _null_;
set test end=last;
array ch(*) _character_;
array mx(100) _temporary_;
length txt $500;
do j = 1 to dim(ch);
mx(j) = max(mx(j),length(ch(j)));
end;
if last then do;
do j=1 to dim(ch);
txt = catx(' ',txt,vname(ch(j)),'$',mx(j));
end;
call symputx('lengths',txt);
end;
run;

*
reread the data, change the length of the character variables
the retain restores the original variable order from the CSV file
;
data test;
retain &vars;
length &lengths;
set test;
run;

proc contents data=test;
ods select variables;
run;

proc print data=test;
run;

topic Re: Alternative to mixed=yes when using Proc Import on a CSV file in SAS Procedures

Alternative to mixed=yes when using Proc Import on a CSV file

Re: Alternative to mixed=yes when using Proc Import on a CSV file