Create several excel sheets that start from different tables in the same code block.

[Abelp9]

Hello, I'm new to programming and I'm trying to automate a process, I'm in a part where my code creates about 160 excel files and sheets are added to these files (each excel sheet, a different code since it starts from a table in different SAS). Before it went well because the destination path of these xlsx files was within SAS, now it is still within SAS but in a bridge folder with windows, that is, the excels are created and automatically when going to the bridge folder, they go from sas and come to windows.

The problem is that as the files are transferred very quickly to windows, in the following block of code where the second sheet would be created, a new excel is created for me since by the time I execute the second piece of code, the files that were generated in the first one is already in windows, and my bridge folder seen from SAS is empty, so the excel files are not renamed and files are created with loose sheets.

What I had thought was to create a slightly larger code where I can create 5 sheets at a time (before there would be 5 pieces of code, 1 for each sheet) and that directly send it to my bridge folder, so that the complete excel files are exported .

Does anyone know if this can be done?

I pass you the code of two sheets that I create and I would like to work, the idea would be to do the same with the other pieces of code.

*MACRO 1;
proc sql noprint;
select distinct 
   cat("DATA I1_",&month.,"_",compress(&COLUMN.,,'kad'),"; set &TABLE.(where=(&COLUMN.='", &COLUMN.,"'));run;
proc export data=I1_",&month.,"_", &COLUMN.," 
	outfile='&out_path.","ST_",  &column.,"_", &fecha., "_S_M7_",&month.,"'
	dbms=xlsx replace;sheet='SHEET 1';run;
;run")length=500 into :allsteps separated by ';' 
  from &TABLE.
quit;


%macro runSteps;
&allsteps.;
%mend;
 %runsteps

*MACRO 2;
proc sql noprint;
select distinct 
   cat("DATA I2_",&month.,"_",compress(&COLUMN.,,'kad'),"; set &TABLE.(where=(&COLUMN.='", &COLUMN.,"'));run;
proc export data=I2_",&month.,"_", &COLUMN.," 
	outfile='&out_path.","S_",  &column.,"_", &fecha., "_S_M7_",&month.,"'
	dbms=xlsx replace;sheet='SHEET 2';run;
;run")length=500 into :allsteps separated by ';' 
  from &TABLE.
quit;

%macro runSteps;
&allsteps.;
%mend;
 %runSteps

I don't know if I have expressed myself clearly, but my intention is to create those 2 sheets in the same code, not having to make a code for one and another code for another, to be able to export the excel with all its sheets to the bridge folder, and do not export loose sheets since they are transferred very quickly to windows.

Thank you very much in advance, greetings

[Kurt_Bremser]

1. Start with simple code that creates the two sheets in a single Excel file (for one instance), with no macro code at all.
2. Identify the parts that need to change for the other instances, and use macro variables for them.
3. Wrap the whole code into a macro definition, where the macro variables are supplied as macro parameters.
4. Set up a dataset containing the parameters for every intended instance.
5. Use the dataset as source for repeated calls of the macro, either with CALL EXECUTE or by writing to a file for later %INCLUDE.

[Abelp9]

I'm new to programming so I don't understand very well what you are telling to me, I will continue searching the internet, thank you very much!

[Kurt_Bremser]

Start with step 1. Let us see what that simple code looks like.

[Reeza]

@Kurt_Bremser suggestion are illustrated here. 

But in your case I don't think you have a working solution first. 

You likely need to create the file somewhere and then move it unfortunately. 

I would recommend adding an FCOPY() at the end of the process, once you're sure the file is created and done and have it moved rather than try to add a separate macro to move it though. 

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Tom]

First step is eliminate the code generation so we can see what code you are trying to run more clearly.

For example you posted this SQL step that appears to be trying to put code into a macro variable.

*MACRO 1;
proc sql noprint;
select distinct 
   cat("DATA I1_",&month.,"_",compress(&COLUMN.,,'kad'),"; set &TABLE.(where=(&COLUMN.='", &COLUMN.,"'));run;
proc export data=I1_",&month.,"_", &COLUMN.," 
	outfile='&out_path.","ST_",  &column.,"_", &fecha., "_S_M7_",&month.,"'
	dbms=xlsx replace;sheet='SHEET 1';run;
;run")length=500 into :allsteps separated by ';' 
  from &TABLE.
quit;

Let's see if we can figure out what code it is you are trying to create with that.

Looks a lot like this might be it:

data I1_MONTH_COLUMN-VALUE;
  set TABLE (where=(COLUMN="COLUMN-VALUE"));
run;
proc export data=I1_MONTH_COLUMN
  outfile="OUT_PATH/ST_COLUMN_FECHA_S_M7_MONTH"
  dbms=xlsx
  replace
;
  sheet='SHEET 1';
run;

Where OUT_PATH, MONTH, FECHA, TABLE and COLUMN are actually being replaced by macro variable values, except in a few places where you are using the actual value of the variable that named by the value of the COLUMN macro variable.

There does not appear to be any need to make dataset.  Why not just add the dataset option to the PROC EXPORT call?

Now you mentioned wanting to combine the multiple spreadsheets you are generating.  What do you think that should look like?  Do you want to write all of the values of COLUMN to the same XLSX workbook, but just into separate sheets?  Or perhaps all of the sheets for that value of COLUMN into the same XLSX workbook but with different sheets for each value of MONTH?  Or perhaps each value of FECHA?