SAS输出CSV技巧、可控变量名称、变量Label等
1、%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: uloop
from csv_tem2 where ^missing(TYP1);
quit;
%put NOTE:This CSV have COL:&uloop.;
2、/*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 then
TYP&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;
3、/*%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;