Export each observation from a dataset using macros do loops

[BrahmanandaRao]

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

[japelin]

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;

[BrahmanandaRao]

Thank you Japelin 

[Tom]

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.

[BrahmanandaRao]

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

[Tom]

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;

[Tom]

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

[BrahmanandaRao]

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

[Tom]

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.

[Kurt_Bremser]

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;

[Sajid01]

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.