topic Re: How do I loop a SQL Query - Different Variables in SAS Procedures

How do I loop a SQL Query - Different Variables

Adam93 — Wed, 17 Oct 2018 08:38:56 GMT

SAS EG

Hi there... I am new to the forum and a first time poster so please go easy !

I have basic understanding of loops from my experience with VBA although I need some help with doing the same in SAS..

My simplified code looks something like this:

****************************************

Proc Import Excel data set;

%let Master_Account = "123456789";

/*step 1*/

proc sql;

create table HISTORY as

select A from Table
where ACCOUNT = &Master_Account;

quit;

/*step 2*/

Proc export to excel Sheet 1;

quit;

****************************************

The "&Master_Account" exists as there are 4 repetitions of the above code with different select queries.

There is a local excel file that contains a list of account numbers that I import and save to 'work' where I then need to loop through each account number and run the above query.

In the end there should be excel reports generated based off the inputted.

Thanks in advance you for your help!

Re: How do I loop a SQL Query - Different Variables

RW9 — Wed, 17 Oct 2018 08:47:08 GMT

Well, whilst there are loops in SAS, what you are trying to do here is not a loop as such, it is called by group processing, and it is simpler. From what you post I gather that you want to produce an Excel file with one tab for each of the groups from History table where it is in a given list. So the steps are (and I have no data to work off here):

/* Import list */
proc import ... out=list;
run;

/* Filter big data */
proc sql;
  create table want as
  select * 
  from   history
  where account in (select account from list);
quit;

/* Produce report */
ods excel file="....xml" options(sheet_interval="bygroup");
proc report data=want nowd;
  by account;
...
run;
ods excel close;

The by is a key line here, as is the sheet_interval option.

That should get you started.

Re: How do I loop a SQL Query - Different Variables

Adam93 — Wed, 17 Oct 2018 11:58:07 GMT

Hi RW9, thanks for a speedy reply 🙂

I should have clarified the output goal better...

The final outcome I am trying to reach is to have a separate excel document for each account.

As it stands, my code will run 4 queries and produce an excel spreadsheet that contains 4 worksheets (for each query).

I can run this code one account at a time for each account, but hoping to speed up the process for large batches...

My thinking was to loop the accounts from my list into the " %let Master_Account = "123456789"; " section of the code.. this would allow the code to generate a separate spreadsheet for each set of queries.

What are your thoughts?

Re: How do I loop a SQL Query - Different Variables

RW9 — Wed, 17 Oct 2018 12:09:33 GMT

In which case use call execute:

/* Import list */
proc import ... out=list;
run;

/* Produce report */
data _null_;
 set list;
 call execute(cats('ods excel file="',account,'.xml";'));
 call execute(cats('proc report data=want nowd; by account; where account="',account,'"; run;'));
 call execute('ods excel close;');
run;

So for each row in list, the ods and proc report gets generated. In the above, the filename is called what is in the row of account from list.