Desktop productivity for business analysts and programmers

populate variables from a table and loop the program

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

populate variables from a table and loop the program

[ Edited ]

I have a project that pulls data from 3 DB2 tables and 1 Oracle table based on a prompt value.  The prompt contains a bank id used to relate all the data together.  This works well after the local prompt is converted to a global prompt.  The value of the prompt can be altered and the proper report produced.

 

The next step is to replace the single prompt with a variable taken from a table (Bank_ID) of more than 10 banks, take one bank id at a time, run the program which produces an Excel report, select the next bank id from the table (Bank_ID), run program, produce new report....

 

I am looking for suggestions on how to take a single field from each observation in the SAS table (Bank_ID) run the project and repeat until all observations are processed. 

 

Due to my lack of experience (learning on the fly) with EG I am struggling with this.

 


Accepted Solutions
Solution
4 weeks ago
Occasional Contributor
Posts: 15

Re: populate variables from a table and loop the program

[ Edited ]
Posted in reply to ChrisHemedinger

What I was looking for is detailed below.  Right now it is a task in a project which uses an %include to execute SAS code to produce 10 reports based on an ID number from a table.  It loops through 10 observations in the table based on a where statement to isolate the 10 observations.

 

Each observation is passed in a variable to the SAS code to query 6 tables in two different operating systems. 

 


/*..Build a dataset to be a row-level driver of a separate SAS program. */
proc sql;
 CREATE TABLE WORK.SCHOOL_ID1 AS
 SELECT *
 FROM WORK.SCHOOL_ID_LIST_01
 WHERE ORG_LIST LIKE 'S%';
quit;

/*..Add a row number to the SCHOOL_ID dataset. */
data WORK.SCHOOL_ID1_Dataset;
  set WORK.SCHOOL_ID1;
  RowNumber = _n_;
run;

/*..Count the number of rows in the SCHOOL_ID dataset. */
proc sql noprint;
  select count(*) into :SCHOOL_ID_RowCount
  from WORK.SCHOOL_ID1_Dataset;
quit;
%put SCHOOL_ID_RowCount = &SCHOOL_ID_RowCount;

%macro SCHOOL_ID_ProcessRow(RowNumber=);
  proc sql noprint;
    select SCHOOL_ID_LIST into :SCHOOL_ID
    from work.SCHOOL_ID1_Dataset
    where RowNumber = &RowNumber;
  quit;
  %put For RowNumber=&RowNumber;
  %put SCHOOL_ID=&SCHOOL_ID;
 
 %include '...include sas code using the variable SCHOOL_ID';

%mend SCHOOL_ID_ProcessRow;

/*..Loop thru each row of the SCHOOL_ID dataset. */
%macro SCHOOL_ID_Loop;
  %do SCHOOL_ID_RowNum = 1 %to &SCHOOL_ID_RowCount;
     %SCHOOL_ID_ProcessRow(RowNumber=&SCHOOL_ID_RowNum);
  %end;
%mend SCHOOL_ID_Loop;
%SCHOOL_ID_Loop;

 

 

View solution in original post


All Replies
Super User
Posts: 23,980

Re: populate variables from a table and loop the program

Posted in reply to RAW_newbie

You probably want a macro and then to use CALL EXECUTE to call the macro from the data set.

 

This is easier via code, no idea how you would loop a process flow. 

 

DO you have a specific program or are you using the process flow?

 

Community Manager
Posts: 3,452

Re: populate variables from a table and loop the program

As Reeza says, there is no looping mechanism in the process flow.  To scale this you will probably need to convert to an all-code approach.

 

@LeonidBatkhan wrote a nice article about how to use CALL EXECUTE in this way.

 

(It is possible to "loop" in EG via automation...but that's probably a topic for another day.)

 

 

Solution
4 weeks ago
Occasional Contributor
Posts: 15

Re: populate variables from a table and loop the program

[ Edited ]
Posted in reply to ChrisHemedinger

What I was looking for is detailed below.  Right now it is a task in a project which uses an %include to execute SAS code to produce 10 reports based on an ID number from a table.  It loops through 10 observations in the table based on a where statement to isolate the 10 observations.

 

Each observation is passed in a variable to the SAS code to query 6 tables in two different operating systems. 

 


/*..Build a dataset to be a row-level driver of a separate SAS program. */
proc sql;
 CREATE TABLE WORK.SCHOOL_ID1 AS
 SELECT *
 FROM WORK.SCHOOL_ID_LIST_01
 WHERE ORG_LIST LIKE 'S%';
quit;

/*..Add a row number to the SCHOOL_ID dataset. */
data WORK.SCHOOL_ID1_Dataset;
  set WORK.SCHOOL_ID1;
  RowNumber = _n_;
run;

/*..Count the number of rows in the SCHOOL_ID dataset. */
proc sql noprint;
  select count(*) into :SCHOOL_ID_RowCount
  from WORK.SCHOOL_ID1_Dataset;
quit;
%put SCHOOL_ID_RowCount = &SCHOOL_ID_RowCount;

%macro SCHOOL_ID_ProcessRow(RowNumber=);
  proc sql noprint;
    select SCHOOL_ID_LIST into :SCHOOL_ID
    from work.SCHOOL_ID1_Dataset
    where RowNumber = &RowNumber;
  quit;
  %put For RowNumber=&RowNumber;
  %put SCHOOL_ID=&SCHOOL_ID;
 
 %include '...include sas code using the variable SCHOOL_ID';

%mend SCHOOL_ID_ProcessRow;

/*..Loop thru each row of the SCHOOL_ID dataset. */
%macro SCHOOL_ID_Loop;
  %do SCHOOL_ID_RowNum = 1 %to &SCHOOL_ID_RowCount;
     %SCHOOL_ID_ProcessRow(RowNumber=&SCHOOL_ID_RowNum);
  %end;
%mend SCHOOL_ID_Loop;
%SCHOOL_ID_Loop;

 

 

Community Manager
Posts: 3,452

Re: populate variables from a table and loop the program

Posted in reply to RAW_newbie

That might work for you, but it could be so much less complex with fewer macro constructs, if you want.

 

First, you can get the count of a list from PROC SQL by assigning the &SQLOBS macro variable, automatically generated.  See How many records are in that PROC SQL result?

 

Next, there are two main patterns that SAS programmers use when having to repeat a process for each record or for every value of a grouping variable:

I think that either one of these would save you from having one macro that has to call another macro within a macro loop.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 136 views
  • 0 likes
  • 3 in conversation