hI @ballardw , @FreelanceReinh , and @PaigeMiller
Thank you for your insightful notices and suggestions!
After grasping your idea, I twist the code quite a bit as below (2 changed lines and one added line noted by comment) (by changing the order of s&i. condition and numeric-type output of s&i.2 if s&i. in: ('NA', '$$', '..') following @ballardw
options compress=yes reuse=yes;
options mergenoby=error;
OPTIONS MPRINT;
%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=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. = " ";/*changed line*/
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 s&i. in: ('NA', '$$', '..') then s&i.2=.;/*changed line*/
else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.); /*added line*/
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*/
filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
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=45);');
call execute(cmd);
end;
keep fname;
run;
To answer @FreelanceReinh , thank you for your comprehensive answer, I tried libgen statement instead of proc import but it does not work in my system because of the error
ERROR: Invalid physical name for library MYXL
I also raise a topic about this error but the answer so far is still ambiguous
https://communities.sas.com/t5/SAS-Programming/Is-there-anyway-to-import-a-file-xlsm-to-xlsx-in-SAS/m-p/709831#M218377
And hi @PaigeMiller , I am sorry about this inconvenience. I also use OPTION MPRINT when running macro, just because when posting this post, I just want it be simplified quite a bit. So, sorry for your experience when reading my log, now I posted the log of the proc beforehand.
This is MPRINT log
PRINT(IMPORTANDTRANSPOSE): proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet1 dbms=
xlsx replace;
MPRINT(IMPORTANDTRANSPOSE): range= "Sheet1$A:X";
MPRINT(IMPORTANDTRANSPOSE): getnames= yes;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc sort data= Arg_sheet1;
MPRINT(IMPORTANDTRANSPOSE): by Type;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet2 dbms=
xlsx replace;
MPRINT(IMPORTANDTRANSPOSE): range= "Sheet2$A:AG";
MPRINT(IMPORTANDTRANSPOSE): getnames= yes;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc sort data= Arg_sheet2;
MPRINT(IMPORTANDTRANSPOSE): by Type;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc transpose data= Arg_sheet2 out= Arg_sheet2_out(rename=(COL1=s2 _NAME_=Year) drop=_label_);
MPRINT(IMPORTANDTRANSPOSE): by Type;
MPRINT(IMPORTANDTRANSPOSE): VAR '1988'N - '2019'N;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc print data=Arg_sheet2_out;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): data Arg_sheet2_outx;
MPRINT(IMPORTANDTRANSPOSE): set Arg_sheet2_out;
MPRINT(IMPORTANDTRANSPOSE): if s2 in: ('NA', '$$', '..') then s22=.;
MPRINT(IMPORTANDTRANSPOSE): else if s2 not in: ('NA', '$$', '..') then s22=input(s2, 32.);
MPRINT(IMPORTANDTRANSPOSE): drop s2;
MPRINT(IMPORTANDTRANSPOSE): rename s22=s2;
MPRINT(IMPORTANDTRANSPOSE): run;
/*.
.
similar from s2 til s45 except s34
.*/
MPRINT(IMPORTANDTRANSPOSE): proc transpose data= Arg_sheet34 out= Arg_sheet34_out(rename=(COL1=s34 _NAME_=Year) drop=_label_);
MPRINT(IMPORTANDTRANSPOSE): by Type;
MPRINT(IMPORTANDTRANSPOSE): VAR '1988'N - '2019'N;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): proc print data=Arg_sheet34_out;
MPRINT(IMPORTANDTRANSPOSE): run;
MPRINT(IMPORTANDTRANSPOSE): data Arg_sheet34_outx;
MPRINT(IMPORTANDTRANSPOSE): set Arg_sheet34_out;
MPRINT(IMPORTANDTRANSPOSE): if s34 in: ('NA', '$$', '') then s34 = " ";
MPRINT(IMPORTANDTRANSPOSE): run;
And the log is as below
+ proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet1
dbms= xlsx replace;
1 +
range= "Sheet1$A:X"; getnames= yes; run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 24 variables.
NOTE: Compressing data set WORK.ARG_SHEET1 decreased size by 0.00 percent.
Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: WORK.ARG_SHEET1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.11 seconds
cpu time 0.11 seconds
1 +
proc
2 + sort data= Arg_sheet1; by Type; run;
NOTE: There were 453 observations read from the data set WORK.ARG_SHEET1.
NOTE: The data set WORK.ARG_SHEET1 has 453 observations and 24 variables.
21 The SAS System 10:21 Wednesday, January 20, 2021
NOTE: Compressing data set WORK.ARG_SHEET1 decreased size by 0.00 percent.
Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
2 + proc import datafile= "C:\Users\pnguyen\Desktop\New
folder\Argentina_.xlsx" out= Arg_sheet2 dbms= xlsx replace;
2 +
range= "Sheet2$A:AG";
3 + getnames= yes; run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET2 decreased size by 66.67 percent.
Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: WORK.ARG_SHEET2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
3 + proc sort data= Arg_sheet2; by Type; run;
NOTE: There were 453 observations read from the data set WORK.ARG_SHEET2.
NOTE: The data set WORK.ARG_SHEET2 has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET2 decreased size by 66.67 percent.
Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
3 + proc
transpose data= Arg_sheet2 out= Arg_sheet2_out(rename=(COL1=s2 _NAME_=Year) drop=_label_); by Type;
VAR
4 + '1988'N - '2019'N; run;
NOTE: There were 453 observations read from the data set WORK.ARG_SHEET2.
NOTE: The data set WORK.ARG_SHEET2_OUT has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET2_OUT decreased size by 46.15 percent.
Compressed is 7 pages; un-compressed would require 13 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
4 + proc print data=Arg_sheet2_out; run;
NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET2_OUT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 6.52 seconds
cpu time 6.09 seconds
22 The SAS System 10:21 Wednesday, January 20, 2021
4 + data Arg_sheet2_outx; set
Arg_sheet2_out; if s2 in: ('NA', '$$', '..') then s22=.; else if s2 not in: ('NA', '$$', '..') then s22=input(s2, 32.);
drop s2;
5 + rename s22=s2; run;
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET2_OUT.
NOTE: The data set WORK.ARG_SHEET2_OUTX has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET2_OUTX increased size by 16.67 percent.
Compressed is 7 pages; un-compressed would require 6 pages.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
/* Eveythingh run smoothly til s35*/
proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet35
101 + dbms= xlsx replace;
101 + range= "Sheet35$A:AG"; getnames= yes; run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET35 decreased size by 0.00 percent.
Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: WORK.ARG_SHEET35 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.17 seconds
cpu time 0.17 seconds
101 +
proc sort data= Arg_sheet35; by Type; run;
NOTE: There were 453 observations read from the data set WORK.ARG_SHEET35.
NOTE: The data set WORK.ARG_SHEET35 has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET35 decreased size by 0.00 percent.
Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
101 +
proc transpose data= Arg_sheet35 out= Arg_sheet35_out
59 The SAS System 10:21 Wednesday, January 20, 2021
102 +(rename=(COL1=s35 _NAME_=Year) drop=_label_); by Type; VAR '1988'N - '2019'N; run;
NOTE: There were 453 observations read from the data set WORK.ARG_SHEET35.
NOTE: The data set WORK.ARG_SHEET35_OUT has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET35_OUT increased size by 33.33 percent.
Compressed is 8 pages; un-compressed would require 6 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
102 + proc print
data=Arg_sheet35_out; run;
NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET35_OUT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 6.54 seconds
cpu time 6.32 seconds
102 +
data Arg_sheet35_outx; set Arg_sheet35_out; if s35 in: ('NA', '$$', '..') then s352=.;
103 + else if s35 not in: ('NA', '$$', '..') then s352=input(s35, 32.); drop s35; rename s352=s35; run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
102:215 103:11
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
103:58
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1988 s35=224981952135 s352=224981952135 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1989 s35=208880107087 s352=208880107087 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1990 s35=203726588309 s352=203726588309 _ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1991 s35=222333162872 s352=222333162872 _ERROR_=1 _N_=4
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
60 The SAS System 10:21 Wednesday, January 20, 2021
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1992 s35=239980394235 s352=239980394235 _ERROR_=1 _N_=5
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1993 s35=259675534968 s352=259675534968 _ERROR_=1 _N_=6
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1994 s35=274830720367 s352=274830720367 _ERROR_=1 _N_=7
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1995 s35=267011210298 s352=267011210298 _ERROR_=1 _N_=8
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1996 s35=281768091695 s352=281768091695 _ERROR_=1 _N_=9
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1997 s35=304622433397 s352=304622433397 _ERROR_=1 _N_=10
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1998 s35=316350941905 s352=316350941905 _ERROR_=1 _N_=11
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1999 s35=305641016669 s352=305641016669 _ERROR_=1 _N_=12
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
61 The SAS System 10:21 Wednesday, January 20, 2021
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2000 s35=303229512290 s352=303229512290 _ERROR_=1 _N_=13
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2001 s35=289860609223 s352=289860609223 _ERROR_=1 _N_=14
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2002 s35=258281789127 s352=258281789127 _ERROR_=1 _N_=15
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2003 s35=281106256201 s352=281106256201 _ERROR_=1 _N_=16
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Over 100 NOTES, additional NOTES suppressed.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
Type=131566 Year=2004 s35=306488951657 s352=306488951657 _ERROR_=1 _N_=17
Type=131566 Year=2005 s35=333618311350 s352=333618311350 _ERROR_=1 _N_=18
Type=131566 Year=2006 s35=360465082298 s352=360465082298 _ERROR_=1 _N_=19
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
Type=131566 Year=2007 s35=392934518438 s352=392934518438 _ERROR_=1 _N_=20
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET35_OUT.
NOTE: The data set WORK.ARG_SHEET35_OUTX has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET35_OUTX increased size by 33.33 percent.
Compressed is 8 pages; un-compressed would require 6 pages.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
I also added the data of the file Argentina_ that I used in my regression, can you please help me to have a look?
... View more
