BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RAW_newbie
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RAW_newbie
Obsidian | Level 7

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

4 REPLIES 4
Reeza
Super User

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?

 

ChrisHemedinger
Community Manager

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

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
RAW_newbie
Obsidian | Level 7

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;

 

 

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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