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

Hello – Using SAS EG 7.1; I’m trying to perform the same set of steps (w/ PROC SQL) based on multiple variables entered via a prompt.

 

Example:  let’s say I have a table with the following 4 fields:  StoreID; StoreName; StoreCity; StoreRevenue

 

There are hundreds of StoreIDs in the table; I want to create an individual report showing StoreName and StoreRevenue for each StoreID entered by the user via a prompt called “SID” (user can enter many).

 

I know that when entered by the prompt, the variables become SID1, SID2, SID3, etc and the total number entered would be SID_count.  But I don’t know how the PROC SQL steps would be included in the following to make it work (or if I’m off base completely).  Any advice would be most appreciated!

 

 

%macro MyLoop(SID_count);

   %do i=1 %to &SID_count;

     

          PROC SQL;

          Etc, etc;

       

  run;

   %end;

%mend MyLoop;

%MyLoop(&SID_count)

1 ACCEPTED SOLUTION

Accepted Solutions
dcruik
Lapis Lazuli | Level 10

It really depends on what you are wanting to display in your reports.  If you just want to display the data and the variables StoreName and StoreRevenue for each StoreID entered, you could use proc sql or just a simple proc print.  The code would look something like:

 

%macro MyLoop(SID_count);

%do i=1 %to &SID_count;
  proc sql;
  title "&&SID&i Report";
  select StoreName, StoreRevenue
  from Store_Data_Set
  where StoreID="&&SID&i";
  quit;
  title;
%end;

%mend MyLoop;

%MyLoop(&SID_count)

Basically, this code would loop through as many times as there are the total number of StoreID's entered, and each time it would produce a report that displays a Title with the StoreID and then the StoreName and StoreRevenue from your data set of all the different stores, but it would filter out only the StoreID requested.

 

The &&SID&i is a macro variable that would resolve to the StoreID.  So, for example, in the first iteration i would equal 1, and in this case &&SID&i would resolve to &SID1 (which is the first StoreID variable that was entered according to your explanation) and that would resolve to the actual value of the StoreID that was entered for in the first line.  Is this what you are looking for?

View solution in original post

8 REPLIES 8
dcruik
Lapis Lazuli | Level 10

It really depends on what you are wanting to display in your reports.  If you just want to display the data and the variables StoreName and StoreRevenue for each StoreID entered, you could use proc sql or just a simple proc print.  The code would look something like:

 

%macro MyLoop(SID_count);

%do i=1 %to &SID_count;
  proc sql;
  title "&&SID&i Report";
  select StoreName, StoreRevenue
  from Store_Data_Set
  where StoreID="&&SID&i";
  quit;
  title;
%end;

%mend MyLoop;

%MyLoop(&SID_count)

Basically, this code would loop through as many times as there are the total number of StoreID's entered, and each time it would produce a report that displays a Title with the StoreID and then the StoreName and StoreRevenue from your data set of all the different stores, but it would filter out only the StoreID requested.

 

The &&SID&i is a macro variable that would resolve to the StoreID.  So, for example, in the first iteration i would equal 1, and in this case &&SID&i would resolve to &SID1 (which is the first StoreID variable that was entered according to your explanation) and that would resolve to the actual value of the StoreID that was entered for in the first line.  Is this what you are looking for?

Orange4
Calcite | Level 5

Great!  Thank you so much.  Worked perfectly in my simple example and in the larger project to which I wanted to apply it.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Alternatively, avoiding messy macro language:

%macro MyLoop (sid);
  proc sql;
     ...
  quit;
%mend MyLoop;

data _null_;
  i=1;
  do while (scan("&SID_LIST.",i,",") ne "");
    call execute(cats('%MyLoop(',scan("&SID_LIST.",i,","),');'));
    i=i+1;
  end; 
run;

This has the benefit of not needing to know how many elements, is one simple datastep, and you could do other things, such as entry checking, excluding invalids and so on.

Orange4
Calcite | Level 5

i really like the simplicity here, but i wasn't able to figure out the syntax within the PROC SQL to call the variable as it loops (i.e. with the other solution i was successful using):

...

WHERE t1.StoreID="&&SID&i"

...

 

 

Another issue i've just realized: the original solution above fails if only one entry is made in the prompt.  I'm guessing i'd have to create an IF statement to deal with that.  Not sure if that should start a new thread. 

really appreciate the help here, being new to SAS and to the community.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You just call the macro variable as with any, no need for double ampersands:

%macro MyLoop (sid);
  proc sql;
    title "&SID. Report";
    select StoreName, StoreRevenue
    from Store_Data_Set
    where StoreID="&SID.";
  quit;
  title;
%mend MyLoop;

data _null_;
  i=1;
  do while (scan("&SID_LIST.",i,",") ne "");
    call execute(cats('%MyLoop(',scan("&SID_LIST.",i,","),');'));
    i=i+1;
  end; 
run;

The call execute generates 1 string of text to call the macro per variable found in sid_list.  Thats the trick, you are using the datastep (which is a loop) to do the looping.

dcruik
Lapis Lazuli | Level 10

If there's just one StoreID entered, you can create an IF statement looking at the value of SID_Count, and if it equals one, just create the same SQL procedure, but change the &&SID&i to equal &SID1 instead.  Then you can have an ELSE statement that has the Do-Loop code.

Orange4
Calcite | Level 5

Ultimately I wasn’t able to get RW9’s suggestion to work; it failed as it couldn’t reconcile "&SID_LIST." in the data step. I wasn’t able to see if/where this variable was being created and I couldn’t figure out if I was just missing an easy adjustment to make it work with my code.

 

Anyway, I did have success with the following (meaning, a report is created for any number of SIDs entered via prompt – including just one):

 

%macro MyLoop(SID_count);

%do i=1 %to &SID_count;

     proc sql;

     title "Report Concerning: " %if &SID_count = 1 %then "&SID"; %else "&&SID&i";

     select StoreName, StoreRevenue

     from Store_Data_Set

     where StoreID = %if &SID_count = 1 %then "&SID"; %else "&&SID&i";

     quit;

     title;

%end;

%mend MyLoop;

%MyLoop(&SID_count)

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

The SID List was referring to the list of values as in "I know that when entered by the prompt, the variables become SID1, SID2, SID3".  I assumed this was a list.  If it is not then:

%macro MyLoop (sid);
  proc sql;
    title "&SID. Report";
    select StoreName, StoreRevenue
    from Store_Data_Set
    where StoreID="&SID.";
  quit;
  title;
%mend MyLoop;

data _null_;
  call execute('%MyLoop(SID);');
  do i=2 to &SID_Count.;
    call execute(cats('%MyLoop(SID',i,');'));
    i=i+1;
  end; 
run;

Although I dont understand why you are doing it this way in the first place.  Surely you can just use a proc report with a by statement and a datastep (this is unchecked as not at work):

data report_data;
  set have (where=(storeid in (<the list of variables>));
run;
proc report data=report_data;
  by storeid;
  title "This is for store id #byval1";
  columns _all_;
...
run;

No need for macros or sql?

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1600 views
  • 0 likes
  • 3 in conversation