topic Re: Issue looping macro specified number of times in SAS Programming

Issue looping macro specified number of times

Jeff_DOC — Thu, 08 Dec 2022 16:45:20 GMT

Good morning.

I need to loop a macro a number of times based on unique values in a data table. I have a table of information from which I'm pulling unique values, counting them and then using those values in my ODS (file names). I've broken out the steps as much as I can to try to find out where I'm going wrong, but I can't seem to solve this.

In the first step I want all distinct values for casel. The second step just counts how many unique values I have. The third step inserts the unique values with the count into a variable. The second step is where the error normally occurs. put(count(*),1.) only seems to work if there are a particular count of unique values. If the count goes over (or under) that limit I get the erorr: ERROR: Invalid value for width specified - width out of range. changing the 1 to a 2 solves the issue but only until the unique value count decreases/increases on some subsequent run. Then I'll get the same error.

I'm at a loss on how to either do this better or to assign the value dynamically based on the unique values. Of course I could be totally misunderstanding the issue here so any help would be much appreciated.

Thank you.

CASE_12

CASEL
1111
2222
3333
4444
5555
6666
7777
8888
9999
1010

/*List the distinct values so I can see and count them.*/
proc sql;
	create table report_casel as
	select distinct casel
	from case_12;

	/*Count the number so I know how many times to loop.*/
	select put(count(*),1.) into :cnt
	from report_casel;

	/*Insert the casel into a variable.*/
	select casel into :casel1 - :casel&&cnt
     from report_casel;
quit;

	/*Create one report for each existing casel.*/
%macro create_reports;
	/*Set the looping initial value.*/
	%let w = 1;
	%do %until (&w. > &cnt.);

/*Do reporting.*/

	%let w = %eval(&w. + 1);
	%end;
%mend;
%create_reports;

Re: Issue looping macro specified number of times

PaigeMiller — Thu, 08 Dec 2022 16:47:28 GMT

%macro create_reports;
proc sql;
    select casel into :casel_values separated by ' ' from case_12;
quit;
%do i = 1 %to %sysfunc(countw(&casel_values));
    %let this_casel=%scan(&casel_values,&i,%str( ));
    /* Do reporting */
%end;
%mend;

Re: Issue looping macro specified number of times

PaigeMiller — Thu, 08 Dec 2022 16:49:16 GMT

Also, in your code, you don't need the PUT at all

select count(*) into :cnt from report_casel;

Re: Issue looping macro specified number of times

ballardw — Thu, 08 Dec 2022 17:50:44 GMT

"The second step is where the error normally occurs. put(count(*),1.) only seems to work if there are a particular count of unique values".

Or course it only works with particular counts. If the count is > 10 the FORMAT you applies has problems showing any value in 1 character. Example:

data _null_;
   file print;
   x= 15;
   put x 1.;
run;

The result is * , which is usually SAS-speak for "you requested more stuff than will fit". There is usually very little reason to format an integer, which I suspect your Count(*) should be returning.

Another approach is to select the distinct values into a single macro variable and parse it such as:

%macro create_reports;
proc sql noprint;
	select distinct casel into: caselist separated by '|'
	from case_12;
quit;

%do i=1 %to %sysfunc(countw(&caselist.,|));
   %let thiscase = %scan(&caselist.,&i,|);

/*Do reporting for each value of &thiscase*/

%end;

%mend;

You may also find the SAS automatic variable &SQLOBS of use. ANY time you run proc sql SAS places the number of resulting rows into the variable &SQLOBS. So this would not bee needed.

select put(count(*),1.) into :cnt
	from report_casel;

proc sql;
	create table report_casel as
	select distinct casel
	from case_12;
quit;
%let cnt=&sqlobs;

Would set the number of distinct rows returned in the first SQL results into the macro varaible &cnt.

Re: Issue looping macro specified number of times

Tom — Thu, 08 Dec 2022 17:36:39 GMT

No need to re-run the query to count, PROC SQL already has stored the count into SQLOBS macro variable.

proc sql noprint;
  select distinct casel into :casel1 - 
    from case_12
  ;
%let cnt=&sqlobs;
quit;

And there is no need to do your own manual incrementing of the index variable. Just use a normal %DO loop.

%macro create_reports;
/*Set the looping initial value.*/
%local w ;
%do w = 1 %to &cnt;
  /*Do reporting.*/
%end;
%mend;

Example you can experiment with:

56   proc sql noprint;
57     select distinct age into :casel1 -
58       from sashelp.class
59     ;
60   %let cnt=&sqlobs;
61   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


62
63   %put NOTE: Found &cnt AGE groups ;
NOTE: Found 6 AGE groups
64
65   %macro test;
66   %do w=1 %to &cnt;
67     %put AGE group &w ia &&casel&w...;
68   %end;
69   %mend test;
70   %test;
AGE group 1 ia 11.
AGE group 2 ia 12.
AGE group 3 ia 13.
AGE group 4 ia 14.
AGE group 5 ia 15.
AGE group 6 ia 16.

Re: Issue looping macro specified number of times

Jeff_DOC — Fri, 09 Dec 2022 00:59:03 GMT

I decided to go with this solution as it seemed the easiest for me to implement. It worked with a bit of finessing on my part. This finessing was due to my own lack of better understanding of macros and looping. The important part is I got it done and I thank you for that. You gave me a solution that not only worked but simplified what I was doing, and I appreciate that.