Hi @SASKiwi , thank you very much, it works well so far without any warnings or errors, however, the result is quite strange while it only show 100 output files as the picture while the actual number of the output file should be 34*3*3=306 results. One of the solutions I can think of is to delete some "not needed" output files, but I am wondering if there is any solution can be done without deleting these files?
Here is the full code so far
options compress=yes reuse=yes;
%macro ImportAndTranspose(
File=
, StartSheet=
, EndSheet=
);
%local i;
%do i = &StartSheet. %to &EndSheet.;
%if &i=1 %then %do;
proc import datafile= "&File."
out= sheet1
dbms= xlsx
replace;
range= "Sheet1$A:X";
getnames= yes;
run;
proc sort data= sheet1;
by Type;
run;
%end;
%else %if &i= &EndSheet. %then %do;
proc import datafile= "&File."
out= sheet&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= sheet&i.;
by Type;
run;
proc transpose data= sheet&i.
out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=sheet&i._out;
run;
data sheet&i._outx;
set sheet&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= sheet&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= sheet&i.;
by Type;
run;
proc transpose data= sheet&i.
out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=sheet&i._out;
run;
DATA sheet&i._outx;
set sheet&i._out;
if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
drop s&i.;
run;
%end;
%end;
%mend;
*Replicate all file 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);
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),',startsheet=1,endsheet=34);');
put cmd = ;
call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;And a further question: because the sheet2 results from Argentina or Colombia all sheet2_outx, which may confuse me later on, I am wondering is there any chance to add something in my code to have the results like: sheet2_Arg_outx and sheet2_Col_outx.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
6696 views
-
17 likes
-
-
