topic Re: DO Loop on list of variables entered via prompt in SAS Programming

DO Loop on list of variables entered via prompt

Orange4 — Wed, 09 Dec 2015 21:31:42 GMT

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)

Re: DO Loop on list of variables entered via prompt

dcruik — Wed, 09 Dec 2015 21:44:56 GMT

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?

Re: DO Loop on list of variables entered via prompt

Orange4 — Thu, 10 Dec 2015 00:19:48 GMT

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

Re: DO Loop on list of variables entered via prompt

RW9 — Thu, 10 Dec 2015 09:54:16 GMT

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.

Re: DO Loop on list of variables entered via prompt

Orange4 — Thu, 10 Dec 2015 22:17:02 GMT

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.

Re: DO Loop on list of variables entered via prompt

RW9 — Fri, 11 Dec 2015 09:28:08 GMT

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.

Re: DO Loop on list of variables entered via prompt

dcruik — Fri, 11 Dec 2015 14:10:43 GMT

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.

Re: DO Loop on list of variables entered via prompt

Orange4 — Tue, 29 Dec 2015 18:30:49 GMT

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)

Re: DO Loop on list of variables entered via prompt

RW9 — Tue, 29 Dec 2015 20:09:10 GMT

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?