topic Export each observation from a dataset using macros do loops in SAS Programming

Export each observation from a dataset using macros do loops

BrahmanandaRao — Mon, 28 Feb 2022 04:16:00 GMT

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

Re: Export each observation from a dataset using macros do loops

japelin — Mon, 28 Feb 2022 04:26:49 GMT

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;

Re: Export each observation from a dataset using macros do loops

Tom — Mon, 28 Feb 2022 04:32:53 GMT

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.

Re: Export each observation from a dataset using macros do loops

BrahmanandaRao — Mon, 28 Feb 2022 04:54:28 GMT

This is my version in SAS ondemands

%put &=sysvlong;

SYSVLONG=9.04.01M6P110718

This is not give any output in log window i how many observations in dataset by using sqlobs statement

proc sql noprint;
select name 
  into :names1- 
  from work.ds
;
%let n=&sqlobs;
quit;

Finally i did not get output same its show empty excel sheet

Re: Export each observation from a dataset using macros do loops

Tom — Mon, 28 Feb 2022 05:04:39 GMT

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;

Re: Export each observation from a dataset using macros do loops

Tom — Mon, 28 Feb 2022 05:08:47 GMT

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

Re: Export each observation from a dataset using macros do loops

BrahmanandaRao — Mon, 28 Feb 2022 05:32:12 GMT

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

Re: Export each observation from a dataset using macros do loops

BrahmanandaRao — Mon, 28 Feb 2022 05:47:15 GMT

Thank you Japelin

Re: Export each observation from a dataset using macros do loops

Kurt_Bremser — Mon, 28 Feb 2022 08:52:01 GMT

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;

Re: Export each observation from a dataset using macros do loops

Sajid01 — Mon, 28 Feb 2022 16:08:59 GMT

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.

Re: Export each observation from a dataset using macros do loops

Tom — Mon, 28 Feb 2022 22:38:52 GMT

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.