Exporting same dataset to multiple excel files

[cccc0111]

Hi there,

I would like to export the dataset to multiple excel files based on a certain variable:

---------------------

proc sql;
create table try as
select distinct make from sashelp.cars;

proc sql;
create table try2 as
select count(make) as aaa from sashelp.cars;

data _null_;
set try;
by make;
call symputx ('make',compress(make,' .'),'g');
run;

data _null_;
set try2;
call symputx('n',aaa);
run;

%macro a;
%do i=1 %to &n;
%let var= %scan(&make,&i,"@");
proc export data=testing (where=(make="&make."))
outfile="C:\Users\&make..xlsx"
dbms=xlsx replace;
sheet="&make." ;
run;
%end;
%mend ;
%a;

------------------------------------------------------

My goal is to get all the 38 excel files with the maker name as the filename.

However, all I am able to get here is the last maker name's file.

Would you please point out where I am missing out here? Many thanks!!

[sustagens]

Here are some improvements/issues you can apply/work on:

• Utilise "select into" to simplify the portion of your code that gets the distinct values and count and then assigns them to macro variables
• Regarding "count(make)", this counts the variable make as a whole, regardless if it is null or is a repeating value. I believe you are after "count (distinct make)"
• Incorrect placing of the closing ")" for the compress function.
  This -> 
  

  call symputx ('make',compress(make,' .'),'g') 

  Should be -> 
  

  call symputx ('make',compress(make,' .','g'))

• You are assigning to macro variable "var", 
  

  %let var= %scan(&make,&i,"@");

  but you are not using it anywhere in your code, looks like you are confusing it with &make
  
• Would also recommend to use descriptive names for macro programs
• Remember to always end your proc sql; with quit;

Try this:

proc sql;
select distinct make,
count (distinct make) 
into 
:make_list separated by ' ', 
:n
from sashelp.cars;
quit;

%put &=make_list;
%put &=n;

%macro export_make;
	%do i=1 %to &n;
		%let make=%scan(&make_list,&i);

		proc export data=testing (where=(make="&make."))
		outfile="C:\Users\&make..xlsx"
		dbms=xlsx replace;
		sheet="&make." ;
		run;
	%end;
%mend export_make;

%export_make;

[JeffMeyers]

I just wanted to point out that this is not true:

Incorrect placing of the closing ")" for the compress function.
This -> call symputx ('make',compress(make,' .'),'g') 
Should be -> call symputx ('make',compress(make,' .','g'))

The 'g' option for the call symputx function specifies that the macro will be a global macro variable.

[sustagens]

Thanks for pointing that out, that is correct (if you use symputx). It is also possible that 'g' was used as a modifier to the compress function.

[cccc0111]

the code works perfect right now and thank you so much for your advise 🙂

[JeffMeyers]

In your %let statement below:

%let var= %scan(&make,&i,"@");

There is a difference between macro %scan and scan.  The delimiter in %scan should be exactly what it is in the macro variable value without quotes if you don't want the quotes as part of the delimiter.  For example if your macro variable make equaled something like: One@Two@Three and you specify "@" as your delimiter it won't actually see three words because the delimiter "@" doesn't appear in the macro text.  Instead you can try:

%let var= %scan(&make,&i,@);

I don't know the value of your MAKE macro variable, but this is one potential issue that came to mind.

I would change your loop code to this:

%macro a;
%do i=1 %to &n;
%let var= %scan(&make,&i,@);
proc export data=testing (where=(make="&var."))
outfile="C:\Users\&var..xlsx"
dbms=xlsx replace;
sheet="&var." ;
run;
%end;
%mend ;
%a;

Since I think you're trying to use that scanned value as the name instead of the whole macro variable.  The macro variable VAR holds the value from the %scan.

[s_lassen]

---

@JeffMeyers wrote:

In your %let statement below:

%let var= %scan(&make,&i,"@");

There is a difference between macro %scan and scan.  The delimiter in %scan should be exactly what it is in the macro variable value without quotes if you don't want the quotes as part of the delimiter.  For example if your macro variable make equaled something like: One@Two@Three and you specify "@" as your delimiter it won't actually see three words because the delimiter "@" doesn't appear in the macro text.

Not correct. %SCAN works just like SCAN in that respect, if you put more than one character as a delimiter parameter, the %scan function treats all characters as possible delimiters. The difference is that %SCAN does not expect text in quotes, if you put the parameter in quotes, the quote characters will also be treated as delimiters. So %SCAN(@one@Two@Three,2,"@") returns Two. But so does %SCAN(@One@"Two"@Three,2,"@"), as the double quote is also considered a delimiter.

---

[Kurt_Bremser]

Make your life easier:

%macro export(make);
proc export
  data=testing (where=(make="&make."))
  outfile="C:\Users\&make..xlsx"
  dbms=xlsx
  replace
;
sheet="&make.";
run;
%mend;

proc sort
  data=testing (keep=make)
  out=control
  nodupkey
;
by make;
run;

data _null_;
set control;
call execute(cats('%nrstr(%export(',make,'))'));
run;

[Reeza]

I illustrate exactly this problem here but with an HTML file, not an Excel file. You can change the ODS to ODS EXCEL and get the exact same results. The file name is dynamic as well. I generally prefer this method over loops and creating multiple macro variables as this is easier to debug and mentally develop.

It even happens to use the exact same input data set.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[s_lassen]

The problem is that CALL SYMPUTX overwrites the previous value for every iteration of the data step, so after the data step has run, &MAKE has the last value encountered in the data step.

There are several ways to solve this. One is simply to create a macro variable for each make:

data _null_;
  set try;
  call symputx (cats('make',_N_),compress(make,' .'),'g');
run;

You will then have a series of macro variables, which can be used in your macro.

But this can be accomplished much simpler, by using SQL SELECT INTO:

proc sql noprint;
  select count(distinct make) into :N trimmed from sashelp.cars;
  select distinct make into :make1-:make&N from sashelp.cars;
quit;

You can then adapt your macro to use the make variables.

But it is better to make a simpler macro, and use SQL to generate a series of macro calls:

%macro a(make);
proc export data=testing (where=(make="&make."))
  outfile="C:\Users\&make..xlsx"
  dbms=xlsx replace;
  sheet="&make." ;
run;
%mend ;

proc sql noprint;
  select distinct cats('%a(',make,')') into :doit separated by ';';
quit;

&doit;