data work.ds;
set sashelp.class;
run;
proc sql;
select count(Name) into :N from work.ds;
select name,age,sex into : names1- from work.ds;
quit;
%macro export_data;
%do i=1 %to &N. ;
proc export data=ds(where=(name="&&names&i."))
outfile="/home/u35263349/My_Folder/&&names&i..xlsx"
dbms=xlsx replace;
run;
%end;
%mend;
%export_data;
I want to export each observation into each excel file but no data shown after export
FYI
Try to increase "." after "&i.".
proc export data=ds(where=(name="&&names&i.."))
outfile="/home/u35263349/My_Folder/&&names&i...xlsx"
dbms=xlsx replace;
run;
%end;
Thank you Japelin
Check the version of SAS you are running. XLSX engine is "recent".
1744 %put &=sysvlong; SYSVLONG=9.04.01M5P091317
I was able to run this code on SAS 9.4M5 on Windows.
proc export
data=sashelp.class(where=(name="Alfred"))
outfile="c:\downloads\Alfred.xlsx"
dbms=xlsx replace
;
run;
And it makes an appropriate file.
Your code making the macro variables is a little confused. There is no need to count in advance of making the macro variables. SQL will count for you.
proc sql noprint;
select name
into :names1-
from work.ds
;
%let n=&sqlobs;
quit;
If your variable has leading spaces then that method of making the macro variables will remove them which might explain your issue with zero observations. You might need to add quotes into the macro variables. But watch out if you are also using those macro variables to generate the filename.
proc sql noprint;
select name
into :names1-
from work.ds
;
%let n=&sqlobs;
quit;
The real problem with your code as @japelin pointed out is that you are making filenames without the .xlsx extension because you did not include enough periods. But that shouldn't cause it to not write the actual data. Perhaps you are just looking at some old file instead of the ones your program generated?
Using the NOPRINT option will prevent PROC SQL from cluttering the output window with the list of names you are just trying to get into macro variables. If you want to see the values of the macro variables in the SAS log then use %PUT statement.
%put &=sqlobs &=n &=name1;
Make sure you haven't somehow gotten the system option OBS set to zero. SAS will sometimes do that in response to some error in an earlier step in your code.
1771 options obs=0; 1772 proc export 1773 data=sashelp.class(where=(name="Alfred")) 1774 outfile="c:\downloads\alfred.xlsx" 1775 dbms=xlsx replace 1776 ; 1777 run; NOTE: The export data set has 0 observations and 5 variables. NOTE: "c:\downloads\alfred.xlsx" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.09 seconds cpu time 0.03 seconds
Thank you Tom
understood your point
data _null_;
set work.ds;
call execute('data ' !! compress(Name) !! ';
set work.ds;
where Name = "' !! Name !! '"; run;');
run;
CAN WE EXPORT ABOVE DATASET USING PROC EXPORT HOW EXOPORT _NULL_ DATASET
The data _NULL_ step does not create any output dataset. It is just using CALL EXECUTE() to stack up some SAS code to run after the data _NULL_ step finishes.
This code works for me (on SAS ODA) and creates non-empty spreadsheets:
data work.ds;
set sashelp.class;
run;
proc sql noprint;
select count(Name) into :N from work.ds;
select name into : names1- from work.ds;
quit;
%macro export_data;
%do i=1 %to &N. ;
proc export
data=ds (where=(name="&&names&i.."))
outfile="~/Anandkvn/&&names&i...xlsx"
dbms=xlsx
replace
;
run;
%end;
%mend;
%export_data;
Hello @BrahmanandaRao
This code works for me too and creates one spreadsheet for every name and having one row of data.
I believe your code is OK.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.