Hi SAS Users,   Today I ran a macro, %macro ImportAndTranspose( File= , cur= , outf= , StartSheet= , EndSheet= ); I have the mprint code is as below: MPRINT(IMPORTANDTRANSPOSE): proc import datafile= "C:\Users\pnguyen\Desktop\New folder\United KingdomEUR" out= United Kingdom_sheet1 dbms= xlsx replace; MPRINT(IMPORTANDTRANSPOSE): range= "Sheet1$A:X"; MPRINT(IMPORTANDTRANSPOSE): getnames= yes; MPRINT(IMPORTANDTRANSPOSE): run; It seems that there is no abnormal thing can be seen by me from such a code. However, when running, there is a problem popping up: 1 + proc import datafile= "C:\Users\pnguyen\Desktop\New folder\United KingdomEUR" out= United 1 !+Kingdom_sheet1 dbms= xlsx replace; range= "Sheet1$A:X"; ______________ 22 202 1 !+getnames= yes; run; ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_. ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: Line generated by the CALL EXECUTE routine. 2 + proc sort data= United Kingdom_sheet1; by Type; run; ______________ 22 202 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT, EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS, NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT, UNIQUEOUT, WKNO, WORKNO. ERROR 202-322: The option or parameter is not recognized and will be ignored. I guess the problem popping up because of the blank in the middle of the out data but I do not know how to deal with it, It seems to me no problem here. It is how I get my input for such a macro, including the outf. 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,'.'); /*fname: United KingdomGBP*/ 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;   I do not post my full code here that I do not want to confuse you, but it is the code 1 from this topic https://communities.sas.com/t5/SAS-Programming/Substitute-codes-generating-different-results-debug/m-p/714815#M220724   Thank you and warm regards! P/S: I want to keep the name "United Kingdom" rather than delete the blank in the middle or put a under space because it relates to my following purposes. ... View more

Hi all SAS Users! It is quite a mess for me to debug the current situation. I really need your helps with this case   I have two codes and they all work well individually so far. However, when I am prudent by running and comparing both of them, the results generated from them are a little bit different.  Even these codes are about dealing with all files in one folder, but to simplify and testable, I just use one file in the folder. Code 1: options compress=yes reuse=yes; options mergenoby=error; OPTIONS MPRINT; %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.); /*if s&i. in: ('NA', '$$', '..') then s&i.2=.; else 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,%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,'.'); /*fname: AustriaEUR*/ 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);'); /*strip(fname) to link File=C:\Users\pnguyen\Desktop\New folder\AustriaEUR*/ call execute(cmd); end; keep fname; run; To this code, the datafile named is AustriaEUR, and I have the output of interest is AUSTRIA_MERGE2   Code 2: 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. = " ";/********/ 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.); /*if s&i. in: ('NA', '$$', '..') then s&i.2=.; else 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*/ 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; The filename now is Austria_ and the output of interest is Aus_merge2   The file Austria_ is also the file AustriaEUR, I just change the name to fit the according to code. However, the output of interest Aus_merge2 is slightly different from AUSTRIA_MERGE2 (different about 300 over 22,000 observations). In particular, there are more observations in the output of code 2 compared to that of code 1.    I try to print the code out and compare it but still have not yet found out what causes this difference.    I really and truly hope that you can help me to sort it out.   Many thanks and warmest regards.     ... View more

Hi SAS Unsers,   This morning when I try to adjust my code, I face a noticeable problem as below.   My code for running through all file in a directory is: filename mydir 'C:\Users\pnguyen\Desktop\New folder'; data _null_; did = dopen('mydir'); do i = 1 to dnum(did); fname = dread(did,i); /*fname: AustriaEUR*/ 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);'); /*strip(fname) to link File=C:\Users\pnguyen\Desktop\New folder\AustriaEUR*/ call execute(cmd); end; keep fname; run; A part of my macro is   %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; So, I test with one file named "AustriaEUR" in the folder "New Folder". I have the PROC MPRINT as below, which is quite strange to me MPRINT(IMPORTANDTRANSPOSE): proc import datafile= "C:\Users\pnguyen\Desktop\New folder\AustriaEUR.xlsx" out= AustriaEUR.x_sheet1 dbms= xlsx replace; MPRINT(IMPORTANDTRANSPOSE): range= "Sheet1$A:X"; MPRINT(IMPORTANDTRANSPOSE): getnames= yes; MPRINT(IMPORTANDTRANSPOSE): run; MPRINT(IMPORTANDTRANSPOSE): proc sort data= AustriaEUR.x_sheet1; MPRINT(IMPORTANDTRANSPOSE): by Type; MPRINT(IMPORTANDTRANSPOSE): run; The MPRINT as above means that outf = AustriaEUR.x_sheet   However, if we substitute to the initial code, we have: fname=AustriaEUR then short_fn= cats(substr(fname, 1,length(fname)-3),'_'); So short_fn=Austria_ And  ',outf=',short_fn,'sheet means that outf=Austria_sheet which is much different from the one shown from OPTION MPRINT (outf = AustriaEUR.x_sheet)   Can you please tell me where I did wrongly. Warm regards!     ... View more

Hi SAS Users,   Previously, I want to generate a new variable short_fn the first three characters of a country name (fname) with an underline "_", so my code is short_fn= cats(substr(fname, 1,3),'_'); Therefore, when fname=Argentina_ => short_fn =ARG_                            fname=UK_            => short_fn=UK__   Now, I want to change the code to be compatible of the new name For example, now the fname are ArgentinaARS, United KingdomGBP, and United StatesUSD   And I want to write code to get two separate variables: short_fn: ARGENTINA, UNITED KINGDOM, UNITED STATES currency: ARS, GBP, USD   Can you please help me to sort it out? (If we can make use of the function cats or substr, it would be great, if not, I am also willing to learn new SAS function)   Many thanks in advance and warmest regards.     ... View more