topic Re: How to replicate work for all sheets in Excel in SAS Programming

How to replicate work for all sheets in Excel

Phil_NZ — Mon, 28 Dec 2020 20:30:39 GMT

Hi everyone,

Today I deal with replicating the same code for all sheets in Excel, just change the number of sheets, For example, the codes for sheets i (i=1,33) are the same.

PROC IMPORT OUT= sheet2 DATAFILE= "C:\Users\pnguyen\Desktop\Argentina - delete.xlsm" 
            DBMS=xlsx REPLACE;
	 RANGE="Sheet2$A:AG";
     GETNAMES=YES;
RUN;

proc sort data=sheet2;
by Type;
run;

proc transpose data=sheet2 out=sheet2_out(rename=(COL1=sheet2 _NAME_=Year) drop=_label_);
   by Type;
   VAR '1988'N - '2019'N;
	transposing;
run;

PROC IMPORT OUT= sheet3 DATAFILE= "C:\Users\pnguyen\Desktop\Argentina - delete.xlsm" 
            DBMS=xlsx REPLACE;
	 RANGE="Sheet3$A:AG";
     GETNAMES=YES;
RUN;

proc sort data=sheet3;
by Type;
run;

proc transpose data=sheet3 out=sheet3_out(rename=(COL1=sheet3 _NAME_=Year) drop=_label_);
   by Type;
   VAR '1988'N - '2019'N;
	transposing;
run;

1. Can you please suggest to me any way to wrap up or simplify the code rather than wrote such a group of code for 33 sheets in one excel file.

2. And a further question is: I have 65 files,each file contains 33 sheets like that, and the code for each file are totally the same, is there any way to work with 65 files by using SAS code rather than deal with each file manually?

Many thanks and warmest regards.

Re: How to replicate work for all sheets in Excel

PhilC — Mon, 28 Dec 2020 21:02:09 GMT

Is it that PROC EXPORT and PROC IMPORT are necessary. Could you use LIBNAME with XLSX engine to import and ODS EXCEL with PROC PRINT to export?

Re: How to replicate work for all sheets in Excel

Phil_NZ — Tue, 29 Dec 2020 00:30:07 GMT

Hi PhilC, I just used the Proc import, not export,
But thank you for your suggestion, I think they are interchangeably used.
I mean, if I use LIBNAME with XLSX, could I make my above code shorter?

Many thanks and cheers.

Re: How to replicate work for all sheets in Excel

Shmuel — Tue, 29 Dec 2020 01:09:39 GMT

If you can change from xlsm to xlsx, try next code:

libname myxl xlsx "C:\Users\pnguyen\Desktop\Argentina - delete.xlsm";

%macro xlread;
      %do i=1 %to 33;
          data sheet&i;
             set myxl.sheet&i;
          run;
          proc sort data=sheet&i; by type; run;
          proc transpose data=sheet&i  out==sheet&i._out
                       (rename=(COL1=sheet&i. _NAME_=Year)  drop=_label_);
               by type;
               VAR '1988'N - '2019'N;
	       transposing;
           run;
    %end;
%mend xlread;
%xlread;

Re: How to replicate work for all sheets in Excel

Phil_NZ — Tue, 29 Dec 2020 03:55:18 GMT

Hi @Shmuel

Thank you for your insightful reply, I tried to change the file's form from xlsm to xlsx already, However, when I try to run the code, there are still some errors happening

libname myxl xlsx "C:\Users\pnguyen\Desktop\Argentina.xlsx";

%macro xlread;
      %do i=2 %to 33;
          data sheet&i;
             set myxl.sheet&i;
          run;
          proc sort data=sheet&i; by Type; run;
          proc transpose data=s&i  out==sheet&i._out
                       (rename=(COL1=sheet&i. _NAME_=Year)  drop=_label_);
               by type;
               VAR '1988'N - '2019'N;
	       transposing;
           run;
		   DATA sheet2_outx;
               set sheet2_out;
               s&i2=input(s2,?? 32.);
           run;
    %end;
%mend xlread;
%xlread;

But the result comes out with a couple of error as below

1                                                          The SAS System                           08:27 Tuesday, December 29, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Loop 33 files';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='C:\Users\pnguyen\OneDrive - Massey University\SAS\29_Dec_redo_all.egp';
6          %LET _CLIENTPROJECTPATHHOST='IT082760';
7          %LET _CLIENTPROJECTNAME='29_Dec_redo_all.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         
29         libname myxl xlsx "C:\Users\pnguyen\Desktop\Argentina.xlsx";
NOTE: Libref MYXL was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: C:\Users\pnguyen\Desktop\Argentina.xlsx
30         
31         %macro xlread;
32               %do i=2 %to 33;
33                   data sheet&i;
34                      set myxl.sheet&i;
35                   run;
36                   proc sort data=sheet&i; by Type; run;
37                   proc transpose data=s&i  out==sheet&i._out
38                                (rename=(COL1=sheet&i. _NAME_=Year)  drop=_label_);
39                        by type;
40                        VAR '1988'N - '2019'N;
41         	       transposing;
42                    run;
43         		   DATA sheet2_outx;
44                        set sheet2_out;
45                        s&i2=input(s2,?? 32.);
46                    run;
47             %end;
48         %mend xlread;
49         %xlread;

NOTE: The import data set has 453 observations and 36 variables.
NOTE: There were 453 observations read from the data set MYXL.sheet2.
NOTE: The data set WORK.SHEET2 has 453 observations and 36 variables.
NOTE: Compressing data set WORK.SHEET2 decreased size by 66.67 percent. 
2                                                          The SAS System                           08:27 Tuesday, December 29, 2020

      Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.06 seconds
      


NOTE: There were 453 observations read from the data set WORK.SHEET2.
NOTE: The data set WORK.SHEET2 has 453 observations and 36 variables.
NOTE: Compressing data set WORK.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.01 seconds
      cpu time            0.00 seconds
      

ERROR: File WORK.S2.DATA does not exist.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Expecting a name.  
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: Line generated by the invoked macro "XLREAD".
49                         by type;                VAR '1988'N - '2019'N;         transposing;            run;
                                                                                  ___________
                                                                                  1

WARNING 1-322: Assuming the symbol TRANSPOSE was misspelled as transposing.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DATA161 may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
NOTE: Compression was disabled for data set WORK.DATA161 because compression overhead would increase the size of the data set.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      


NOTE: Line generated by the invoked macro "XLREAD".
49                                                                                                                  DATA sheet2_outx
49       ! ;                set sheet2_out;                s&i2=input(s2,?? 32.);            run;
                                                           _
                                                           180
WARNING: Apparent symbolic reference I2 not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Can you please help me to sort it out?

Many thanks and regards.

Re: How to replicate work for all sheets in Excel

Phil_NZ — Tue, 29 Dec 2020 04:07:26 GMT

I mean, how to deal with the error :"22: LINE and COLUMN cannot be determined."

Thank you!

Re: How to replicate work for all sheets in Excel

Phil_NZ — Tue, 29 Dec 2020 04:14:11 GMT

And how can I import the data range as the first code in this macro code

	 RANGE="Sheet2$A:AG";
     GETNAMES=YES;

Thank you!

Re: How to replicate work for all sheets in Excel

Shmuel — Tue, 29 Dec 2020 07:37:23 GMT

1) I noticed that you added a step to the macro updating SHEET2_OUT dataset,

while the macro creates sheet&i._out dataset. Was it intentional ?

The code s&i2 will result, for any i with an unresolved macro variable.

In order to resolve for i=2 as s22 or for i=3 as s32, code it as s&i.2

The added dot defines the end of macro variable name.

2) According to next log messages:

22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.

and in order to debug the macro precede the macro execution (%xlread;) with

the next line:

options spool mprint symbolgen;

3) To relate to a range of a sheet you can drop the excess columns.

Re: How to replicate work for all sheets in Excel

andreas_lds — Tue, 29 Dec 2020 06:47:22 GMT

@Phil_NZ wrote:

Hi everyone,

Today I deal with replicating the same code for all sheets in Excel, just change the number of sheets, For example, the codes for sheets i (i=1,33) are the same.

[...]

1. Can you please suggest to me any way to wrap up or simplify the code rather than wrote such a group of code for 33 sheets in one excel file.

2. And a further question is: I have 65 files,each file contains 33 sheets like that, and the code for each file are totally the same, is there any way to work with 65 files by using SAS code rather than deal with each file manually?

Many thanks and warmest regards.

You could wrap the three steps in a macro-loop (code is untested):

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;
      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=sheet&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
   %end;
%mend;

%macro ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\Argentina - delete.xlsm
      , StartSheet= 1
      , EndSheet= 33
   );

The answer of the second question depends on the way the files are stored. If they are in one directory, you could use a data step to get the filenames and use call execute to call the macro for each file. Or you could add another macro-loop to read the filenames. If files exist in the directory that must not be processed, you need rules to get the right files.

Re: How to replicate work for all sheets in Excel

Phil_NZ — Tue, 29 Dec 2020 08:44:19 GMT

Hi @andreas_lds !

Thank you for your help. I understand fully your code. However, when I try to run it, it does not work

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;
      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=sheet&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=sheet&i._out;
	  run;
   %end;
   
%mend ImportAndTranspose;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\Argentina.xlsx
      , StartSheet= 2
      , EndSheet= 5)

I also attach the excel file here, can you please have a look?

Regarding your further question, all the files are in one folder, every file is a country. Can you give me a suggestion about macro-loop to read the file names (for all countries) in this folder?

Many thanks and best regards.

Re: How to replicate work for all sheets in Excel

Shmuel — Tue, 29 Dec 2020 09:05:55 GMT

1) Saying "when I try to run it, it does not work" it is not enough information.

You should either explain what you got or post the log.

I suspect that you miss a semicolon closing the macro execution ?!

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\Argentina.xlsx
      , StartSheet= 2
      , EndSheet= 5);    /* semicolon added */

if this is not the case run with a preceding options mprint symbolgen; and post the log.

2) The code @andreas_lds posted tries to keep your original code converting it to a macro program and using xlsx instead xlsm format/engine.

Re: How to replicate work for all sheets in Excel

Phil_NZ — Sat, 02 Jan 2021 09:08:21 GMT

Hi @andreas_lds and @Shmuel for your insightful idea.

Now I have the code for replicating work for all sheet in one file, and I also get the code for retrieving all file name in one folder, can you please give me a hint to run do-loop or else to replicate the given code to all files in this folder? I mean, running the macro "ImportandTranspose" for sheet 1 to 34 of all files in the "NewFolder" folder.

%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;

*Get filename;
   data filenames;
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);
  output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

Many thanks and warmest regards!

Re: How to replicate work for all sheets in Excel

Shmuel — Sat, 02 Jan 2021 09:38:57 GMT

To run macro %ImportAndTranspose for each fname in filenames dataset,

supposing all of them are xlsx format and all sheets are as expected, you can submit the macro within the same step creating the filenames dataset, either by using call execute() function or by creating a program file to be executed in next step:

/* Option-1 - Get filenames and execute the macro */
data _null_;  /* instead filenames; */
	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);
	   /* output; << to be replaced withe: */
	   cmd = catx(',', '%ImportAndTranspose(File=',
	         strip(fname),'startsheet=1,endsheet=34);');
	   call execute(cmd);
	end;
	did = dclose(did);
	did = filename(fref);
keep fname;
run;

Alternative way, enables check the generated program before execution:

/* Option-2 - Get filenames and execute the macro */
filename program tmp 'tmp.sas';
data _null_;  /* instead filenames; */
	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);
	   /* output; << to be replaced withe: */
	   cmd = catx(',', '%ImportAndTranspose(File=',
	         strip(fname),'startsheet=1,endsheet=34);');
	   file program;
	   put cmd;
	end;
	did = dclose(did);
	did = filename(fref);
keep fname;
run;
%include program;

Re: How to replicate work for all sheets in Excel

Phil_NZ — Sun, 03 Jan 2021 01:21:53 GMT

Hi @Shmuel , many thanks for your idea and suggestion, I also have further question that:

	did = dclose(did);
	did = filename(fref);

I know that "dclose" is to close the open directory and any open members. But I do not fully understand why we need "did=filename(fref)" at the end, what does it stand for?

And a question of curiousity, apart from using call execute (cmd), is there any way that can be used in this case instead?

Many thanks!

Re: How to replicate work for all sheets in Excel

Tom — Sun, 03 Jan 2021 01:53:10 GMT

DCLOSE() closes the directory. There are no members that were opened that need to close.

FILENAME() without out any filename eliminates the fileref that was previously associated with the file (the fileref was actually associated with the directory in this case).

Re: How to replicate work for all sheets in Excel

Phil_NZ — Sun, 03 Jan 2021 01:53:49 GMT

Hi @andreas_lds , @Shmuel , another question is about the ERROR existing when running the macro called "All positional parameters must precede keyword parameters", could you please tell me how to sort it out?

Here is my code:

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=catx(',', '%ImportAndTranspose(File=',
      strip(fname), 'startsheet=1,endsheet=34);');
  call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

The log is as below:

1                                                          The SAS System                              14:42 Sunday, January 3, 2021

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='replicate code for all sheets -included s34 _inc filenamer';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='C:\Users\pnguyen\OneDrive - Massey University\SAS\29_Dec_redo_all.egp';
6          %LET _CLIENTPROJECTPATHHOST='IT082760';
7          %LET _CLIENTPROJECTNAME='29_Dec_redo_all.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         options compress=yes reuse=yes;
29         
30         %macro ImportAndTranspose(
31               File=
32               , StartSheet=
33               , EndSheet=
34            );
35         
36            %local i;
37         
38            %do i = &StartSheet. %to &EndSheet.;
39         
40             %if &i=1 %then %do;
41         	
42             proc import datafile= "&File."
43                           out= sheet1
44                           dbms= xlsx
45                           replace;
46                   range= "Sheet1$A:X";
47                   getnames= yes;
48               run;
49         
50               proc sort data= sheet1;
51               by Type;
52               run;
53         	%end;
54         
55         	  %else %if &i= &EndSheet. %then %do;
56               proc import datafile= "&File."
57                           out= sheet&i.
2                                                          The SAS System                              14:42 Sunday, January 3, 2021

58                           dbms= xlsx
59                           replace;
60                   range= "Sheet&i.$A:AG";
61                   getnames= yes;
62               run;
63         
64               proc sort data= sheet&i.;
65               by Type;
66               run;
67         
68               proc transpose data= sheet&i.
69                     out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
70                  by Type;
71                  VAR '1988'N - '2019'N;
72               run;
73         	  proc print data=sheet&i._out;
74         	  run;
75         
76         	  data sheet&i._outx;
77         	  set sheet&i._out;
78               if s&i. in: ('NA', '$$') then s&i. =".";
79         	  run;
80         
81         	  %end;
82         
83         
84             %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
85               proc import datafile= "&File."
86                           out= sheet&i.
87                           dbms= xlsx
88                           replace;
89                   range= "Sheet&i.$A:AG";
90                   getnames= yes;
91               run;
92         
93               proc sort data= sheet&i.;
94               by Type;
95               run;
96         
97               proc transpose data= sheet&i.
98                     out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
99                  by Type;
100                 VAR '1988'N - '2019'N;
101              run;
102        	  proc print data=sheet&i._out;
103        	  run;
104        
105              DATA sheet&i._outx;
106              set sheet&i._out;
107        
108              if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
109        	  drop s&i.;
110              run;
111        	  %end;
112        
113        
114        
115           %end;
3                                                          The SAS System                              14:42 Sunday, January 3, 2021

116        
117        %mend;
118        
119        *Replicate all file in one folder;
120           data _null_;
121        length fref $8 fname $200;
122        did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
123        did = dopen(fref);
124        do i = 1 to dnum(did);
125          fname = dread(did,i);
126          cmd=catx(',', '%ImportAndTranspose(File=',
127              strip(fname), 'startsheet=1,endsheet=34);');
128          call execute(cmd);
129        end;
130        did = dclose(did);
131        did = filename(fref);
132        keep fname;
133        run;

ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1         +startsheet=1,endsheet=34);
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
2         +startsheet=1,endsheet=34);
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
3         +startsheet=1,endsheet=34);
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

134        
135        %LET _CLIENTTASKLABEL=;
136        %LET _CLIENTPROCESSFLOWNAME=;
137        %LET _CLIENTPROJECTPATH=;
138        %LET _CLIENTPROJECTPATHHOST=;
139        %LET _CLIENTPROJECTNAME=;
140        %LET _SASPROGRAMFILE=;
141        %LET _SASPROGRAMFILEHOST=;
4                                                          The SAS System                              14:42 Sunday, January 3, 2021

142        
143        ;*';*";*/;quit;run;
144        ODS _ALL_ CLOSE;
145        
146        
147        QUIT; RUN;
148

While I run the code of reading all filenames only, the code work successfully:

data filenames;
length fref $8 fname $200;
did = filename(fref,'H:\');
did = dopen(fref);
do i = 1 to dnum(did);
  fname = dread(did,i);
  output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

So I thought the problem occurring due to the added function called (execute) or else.

Many thanks and cheers!

Re: How to replicate work for all sheets in Excel

SASKiwi — Sun, 03 Jan 2021 03:12:16 GMT

@Phil_NZ - I've tweaked your command string - hopefully that will work better. If not add a - put cmd = ; - statement to check the syntax:

  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=',
      strip(fname), ', startsheet=1, endsheet=34);');
  call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

Re: How to replicate work for all sheets in Excel

Phil_NZ — Sun, 03 Jan 2021 03:25:55 GMT

Thank you very much for your help, @SASKiwi .

It seems that I no longer face the same problem, but also raising some other errors, I am wondering what is the problem there.

Here is the log:

43         options compress=yes reuse=yes;
44         
45         %macro ImportAndTranspose(
46               File=
47               , StartSheet=
48               , EndSheet=
49            );
50         
51            %local i;
52         
53            %do i = &StartSheet. %to &EndSheet.;
54         
55             %if &i=1 %then %do;
56         	
57             proc import datafile= "&File."
2                                                          The SAS System                              14:42 Sunday, January 3, 2021

58                           out= sheet1
59                           dbms= xlsx
60                           replace;
61                   range= "Sheet1$A:X";
62                   getnames= yes;
63               run;
64         
65               proc sort data= sheet1;
66               by Type;
67               run;
68         	%end;
69         
70         	  %else %if &i= &EndSheet. %then %do;
71               proc import datafile= "&File."
72                           out= sheet&i.
73                           dbms= xlsx
74                           replace;
75                   range= "Sheet&i.$A:AG";
76                   getnames= yes;
77               run;
78         
79               proc sort data= sheet&i.;
80               by Type;
81               run;
82         
83               proc transpose data= sheet&i.
84                     out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
85                  by Type;
86                  VAR '1988'N - '2019'N;
87               run;
88         	  proc print data=sheet&i._out;
89         	  run;
90         
91         	  data sheet&i._outx;
92         	  set sheet&i._out;
93               if s&i. in: ('NA', '$$') then s&i. =".";
94         	  run;
95         
96         	  %end;
97         
98         
99             %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
100              proc import datafile= "&File."
101                          out= sheet&i.
102                          dbms= xlsx
103                          replace;
104                  range= "Sheet&i.$A:AG";
105                  getnames= yes;
106              run;
107        
108              proc sort data= sheet&i.;
109              by Type;
110              run;
111        
112              proc transpose data= sheet&i.
113                    out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
114                 by Type;
115                 VAR '1988'N - '2019'N;
3                                                          The SAS System                              14:42 Sunday, January 3, 2021

116              run;
117        	  proc print data=sheet&i._out;
118        	  run;
119        
120              DATA sheet&i._outx;
121              set sheet&i._out;
122        
123              if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
124        	  drop s&i.;
125              run;
126        	  %end;
127        
128        
129        
130           %end;
131        
132        %mend;
133        
134        *Replicate all file in one folder;
135           data _null_;
136        length fref $8 fname $200;
137        did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
138        did = dopen(fref);
139        do i = 1 to dnum(did);
140          fname = dread(did,i);
141          cmd=cats(%ImportAndTranspose(File=',
142              strip(fname),',startsheet=1,endsheet=34);');
NOTE: Line generated by the invoked macro "IMPORTANDTRANSPOSE".
142        proc import datafile= "&File."                    out= sheet1                   dbms= xlsx                    replace;
                ______                                       ___                                                         _______
                388                                          22                                                          388
                202                                                                                                      76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, 
              GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

142      ! proc import datafile= "&File."                    out= sheet1                   dbms= xlsx                    replace;
                                                                                           ____
                                                                                           22
142      !         range= "Sheet1$A:X";           getnames= yes;       run;              proc sort data= sheet1;       by Type;

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, 
              GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.  

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


ERROR: File WORK.SHEET1.DATA does not exist.

4                                                          The SAS System                              14:42 Sunday, January 3, 2021

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      



ERROR: Physical file does not exist, C:\WINDOWS\system32\,      strip(fname),.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

ERROR: File WORK.SHEET2.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


ERROR: File WORK.SHEET2.DATA does not exist.

And a further concern is: can I ask where I can write

put cmd = ;

to check to the syntax in case needed ?

Many thanks and warmest regards.

Re: How to replicate work for all sheets in Excel

SASKiwi — Sun, 03 Jan 2021 03:37:25 GMT

@Phil_NZ - This is how to check your command - it will just write the contents of cmd to your SAS log:

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=',
      strip(fname), ', startsheet=1, endsheet=34);');
  put cmd = ;
  * call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

Re: How to replicate work for all sheets in Excel

SASKiwi — Sun, 03 Jan 2021 03:42:26 GMT

@Phil_NZ - Oops there's a quote missing:

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=',
      strip(fname), ', startsheet=1, endsheet=34);');
  put cmd = ;
  * call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;