SAS Loop over dataset for create export files

[Gciocia]

Good morning all,

i have a SAS Work table and i'm trying to loop this one in order to launch 1 proc export for every row in this work file:

File UTENTI:

utente                                 email

user1                                  user1@example.com

user2                                  user2@example.com

I tried this code, but it doesn't work:

/* getting numbers of occurencies*/

proc sql;
select count(*) into :nb from WORK.UTENTI;
quit;

/* set eof record number */
%let nb = &nb;

data t;
set WORK.UTENTI;
do i=1 to &nb;
put "DBUSER=&utente";

proc sql;
create table EXPORTEXCEL as
select * from WORK.FILTER_FOR_QUERY_FOR_RE2PRABA_00
where FILIALE IN(SELECT filiale FROM WORK.UTENTI_FILIALI WHERE FILIALE=filiale and utente = &DBUSER);
run;

proc export
data=WORK.EXPORTEXCEL
outfile="C:\SasOutputs\Mail\RSK_001_Pratiche_A_Rischio_&DBUSER.xlsx"
replace
dbms=xlsx;
run;

end;
run;

In this example, i need to loop 2 times selecting data for user1 and user2. I want to make this procedure dynamics no matter what the occurencies will be.

Someone can give me an advice?

Thanks

Gianluca

[RW9 · Accepted solution]

No need for a loop here, a dataset is a loop:

data _null_;
  set work.utenti;
  call execute(cats('proc export data=',utente,' outfile="c:\sasoutputs\mail\rsk_001_practiche_a_reichio_',email,'xls"; run;'));
run;

That will generate a proc export step for every row in the dataset.  From that you can work out your specifics, but it avoids all the find number of obs, do loop etc.

[Gciocia]

Thanks a lot RW9, it worked!

[VimalManavalan]

i tried and got below error. do i need to modify the code with some syntax issue

[VimalManavalan]

can this technique be possible to apply this question .

https://communities.sas.com/t5/New-SAS-User/program-to-export-excel-automatically/m-p/591063#M15146

[RW9]

Please avoid re-opening old questions.  I will post update in your other post.