SAS软件
Excel
%macro csv_csv2sas(path=,csvname=,colmax=%str(10000),outds=,encoding=gb2312,varr=0,labelr=1,length=500);/*options mprint macrogen;*/options nomprint nomacrogen;options nonotes ;%if '%upcase(%scan(&csvname.,-1,'.'))'^='CSV' AND'%upcase(%scan(&csvname.,-1,'.'))'^='TXT' %then %do;%put NOTE:Please enter the correct file type.This Macro support .CSV /.txt;%goto exit;%end;options notes;%if %length(&encoding.)=0 %then %do;%let _encoding=gb2312;%end;%else %if '%upcase(&encoding.)'='GB' or '%upcase(&encoding.)'='GB2312' %then %do;%let _encoding=GB2312;%end;%else %if '%upcase(&encoding.)'='EN' or '%upcase(&encoding.)'='UTF' or'%upcase(&encoding.)'='UTF-8' %then %do;%let _encoding=utf-8;%end;filename csvfn '&path.\&csvname.' encoding='&_encoding.'; %if '%upcase(%scan(&csvname.,-1,'.'))'='CSV' %then %do;data &outds. ;%let _EFIERR_ = 0;infile csvfn delimiter = ',' MISSOVER DSD lrecl=1000000 firstobs=1 obs=3 ;informat %do i=1 %to &colmax.; %sysfunc(compress(Var&i.)) $800. %end;;format %do i=1 %to &colmax.; %sysfunc(compress(Var&i.)) $800. %end;;input %do i=1 %to &colmax.; %sysfunc(compress(Var&i.)) $ %end;;if _ERROR_ then call symput('_EFIERR_',1);run; %end;%if '%upcase(%scan(&csvname.,-1,'.'))'='TXT' %then %do;proc import datafile = csvfn out = &outds dbms = tab replace;delimiter = ',';getnames = no;run;%end;options notes ;/*find the CSV Max Col for next data file*/data csv_tem1;set &outds.;if _N_<3;proc transpose data=csv_tem1 out=csv_tem2 prefix=TYP;by notsorted ;var _all_;run;proc sql noprint;select Max(input(compress(_NAME_,'','kd'),best.)) into: uloopfrom csv_tem2 where ^missing(TYP1);quit;%put NOTE:This CSV have COL:&uloop.;
/*Set Length And Format */data csv_tem2;set csv_tem2;if input(compress(_NAME_,'','kd'),best.) >&uloop. then delete;_format=catx(' ',_NAME_,'$&length..');_input=catx(' ',_NAME_,'$');%if &labelr.=1 or &labelr.=2 %then %do;TYP&labelr.=compress(TYP&labelr.,'','s');len=length(TYP&labelr.);if mod(count(TYP&labelr.,'''),2)=0 thenTYP&labelr.=tranwrd(strip(TYP&labelr.),''','''');else TYP&labelr.=tranwrd(strip(TYP&labelr.),''',''''');_label=catx('=',_NAME_,compress(strip(''')||Strip(TYP&labelr.)||strip(''')));%end;%if &varr.=1 or &varr.=2 %then %do;TYP&varr.=compress(tranwrd(strip(TYP&varr.),' ','_'));_rename=catx('=',_NAME_,compress(TYP&varr.));%end;run;
/*%goto exit;*/ data _null_;set csv_tem2;%if &labelr.=1 or &labelr.=2 %then %do;call symput('_label'||compress(put(_n_,best.)),strip(_label)); %end;%if &varr.=1 or &varr.=2 %then %do;call symput('_rename'||compress(put(_n_,best.)),strip(_rename)); %end;call symput('_format'||compress(put(_n_,best.)),strip(_format)); call symput('_input'||compress(put(_n_,best.)),strip(_input));run;%let _lrc=%eval(&length.*&uloop.);%put NOTE:&_lrc.; data &outds. ;%let _EFIERR_ = 0;infile csvfn delimiter = ',' MISSOVER DSD lrecl=&_lrc. firstobs=1 ;informat %do i=1 %to &uloop.;%sysfunc(compress(&&_format&i.)) %end;;;format %do i=1 %to &uloop.;%sysfunc(compress(&&_format&i.)) %end;;;input %do i=1 %to &uloop.;%sysfunc(compress(&&_input&i.)) %end;;;%if &varr.=1 or &varr.=2 %then %do;rename %do i=1 %to &uloop.;%sysfunc(compress(&&_rename&i.)) %end;;;%end;%if &labelr.=1 or &labelr.=2 %then %do;label %do i=1 %to &uloop.;%sysfunc(compress(&&_label&i.)) %end;;;%end;if _ERROR_ then call symput('_EFIERR_',1);run; proc delete data=csv_tem1 csv_tem2 ;quit;options notes;;%exit:%mend;
SAS读入CSV数据
SAS Macro