Macro-csv2sas(可控制变量Length)

说明

目的: 将CSV数据导入到SAS中

参数说明：

Path 路径

csvname 文件名（支持csv,txt格式）

outds 输出数据集名称

colmax 预估csv列数

encoding csv文件编码格式（支持UTF-8与GB2312）

Varr 变量所在的行：如不需要则填0

labelr 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.;

/*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;

/*%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;

可到百度网盘下载：Macro Code 网盘：https://pan.baidu.com/s/1pLMHdub