topic Re: SAS Loop over dataset for create export files in SAS Programming

SAS Loop over dataset for create export files

Gciocia — Thu, 28 Feb 2019 10:25:55 GMT

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

Re: SAS Loop over dataset for create export files

RW9 — Thu, 28 Feb 2019 10:34:19 GMT

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.

Re: SAS Loop over dataset for create export files

Gciocia — Tue, 05 Mar 2019 08:48:45 GMT

Thanks a lot RW9, it worked!

Re: SAS Loop over dataset for create export files

VimalManavalan — Tue, 24 Sep 2019 04:26:04 GMT

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

Re: SAS Loop over dataset for create export files

VimalManavalan — Tue, 24 Sep 2019 04:27:33 GMT

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

Re: SAS Loop over dataset for create export files

RW9 — Mon, 30 Sep 2019 07:58:53 GMT

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