How to replicate work for all sheets in Excel

[Phil_NZ]

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.

[PhilC]

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?

[Phil_NZ]

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.

[Shmuel]

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;

[Phil_NZ]

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.

[Phil_NZ]

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

Thank you!

[Phil_NZ]

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

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

Thank you!

[Shmuel]

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.

[andreas_lds]

---

@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.

[Phil_NZ]

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.

[Shmuel]

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.

[Phil_NZ]

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!

[Shmuel]

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;

[Phil_NZ]

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!

[Tom]

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).