Hi @Shmuel !
I really appreciate your detailed explanation, and I learn a lot from that; however, I hope that we can make it clear a little bit then.
1. Even when I change from 10 to 9 as the code you mentioned, such warnings still exist.
So, I try to point out what really happended.
2. Let's have a look back about the whole code in general:
%macro ImportAndTranspose(
File=
, 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= &EndSheet. %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 Endsheet.) %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. not in: ('NA', '$$') then 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,4);
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*/
data _null_;
length fref $8 fname $200;
did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
short_fn= cats(substr(fname, 1,3),'_');
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),',outf=',short_fn,'sheet,startsheet=1,endsheet=34);');
call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;
So, for example, the filename( fref) is Argentina_, then fname= Argentina_, then short_fn=Arg_, then outf=Arg_sheet
Therefore, when talking about the code under controversial:
if the code is
%let outfm = %substr(&outf,1,4)
then outfm=Arg_, then when merging, I will have two files Arg_merge1 and Arg_merge2 (because the data step is &outfm.merge1 and &outfm.merge2).
On the other hand, if we follow the code is
%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-9));
At that time, length(outf)=length(Arg_sheet)=9, and then, the 2nd %substr become redundant.
I am wondering but I think the way I present the code confuse you. I think outf in the code
%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-9));
should be Arg_sheet rather than Arg_sheetmerge1.
I hope that I can receive your idea about that.
Many thanks and warmest regards!
... View more
