topic populate variables from a table and loop the program in SAS Enterprise Guide

populate variables from a table and loop the program

RAW_newbie — Tue, 10 Jul 2018 17:39:30 GMT

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.

Re: populate variables from a table and loop the program

Reeza — Tue, 10 Jul 2018 17:45:34 GMT

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?

Re: populate variables from a table and loop the program

ChrisHemedinger — Tue, 10 Jul 2018 17:49:00 GMT

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.)

Re: populate variables from a table and loop the program

RAW_newbie — Fri, 20 Jul 2018 19:27:51 GMT

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;

Re: populate variables from a table and loop the program

ChrisHemedinger — Fri, 20 Jul 2018 19:38:55 GMT

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.