Hi SAS Users,
As we know Proc import causing quite a bit of noise. However, it is my way of importing excel files into SAS at the initial stage, afterwards, I can use libname to refer to the file.
And one of the issues is that the length and data types are inconsistent.
I am wondering if you can have a look to make the data consistent regarding Type and length in my code as below:
options compress=yes reuse=yes;
options mergenoby=error;
OPTIONS MPRINT;
/*options validvarname=any; in case transform to SAS Base*/
%macro ImportAndTranspose(
File=
, cur=
, outf=
, StartSheet=
, EndSheet=
);
%local i;
%do i = &StartSheet. %to &EndSheet.;
%if &i=1 %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet1$A:X";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
%end;
%else %if &i=34 %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
proc transpose data= &outf.&i.
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=&outf.&i._out;
run;
data &outf.&i._outx;
set &outf.&i._out;
if s&i. in: ('NA', '$$', '') then s&i. = " ";/********/
run;
%end;
%else %if (&i ne 1) and (&i ne 34) %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
proc transpose data= &outf.&i.
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=&outf.&i._out;
run;
data &outf.&i._outx;
set &outf.&i._out;
if input(s&i., ?? 32.) = . then s&i.2=.;
else s&i.2=input(s&i., 32.);
drop s&i.;
rename s&i.2=s&i.;
run;
%end;
%end;
/* Merging*/
%let outfm = %substr(&outf,1,%length(&outf)-5);
data &outfm.merge1;
merge
%do i =&StartSheet.+1 %to &EndSheet.;
&outf.&i._outx(keep= type year s&i.)
%end;
;
by type year;
run;
data &outfm.merge2;
merge
&outf.&StartSheet.
&outfm.merge1
;
by type;
run;
%mend;
/*Replicate all files in one folder*/
filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
fname = scan(dread(did,i),1,'.');
/*filename:ARGENTINAARS*/
length short_fn $29 currency $3 ;
short_fn= cats(substr(fname, 1,length(fname)-3),'_');
currency=substr(fname,length(fname)-2);
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),
',cur=',currency,
',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
call execute(cmd);
end;
keep fname;
run;
Many thanks and warmest regards.
... View more
