topic Re: iterate (through macro) in SAS Programming

iterate (through macro)

sahoositaram555 — Tue, 21 Jul 2020 18:21:03 GMT

Hi, In the process of macro building , I'm stuck at a point.

I have a dataset out_surv where var has values such as

Var

;

I have created "&start" and "&end" macro variable from out_Surv. So, they have 0 and 3 subsequently.

In addition to this, I've 3 other globally defined macro vars already stored for the session named &S0, &S1, &S3.

I have to work in a dataset(named Surv which has an existing column Sep) where, by a help of a loop I have to iterate so that when Sep=0 then my newly created variable named total=&S0. similarly, when Sep=1 then total=&S1 and Sep=3 then total=&S3

Surv data set looks like

Sep

I have tried this below:

proc sql;
select min(var),max(var) into :min_var1, :max_var1 
from out_surv;
quit;



%do i=&min_var1 %to &max_var1
data new_dataset;
set Surv;

       %if Sep=&min_sepstrata. then total=&v0.;

          i+1;   ******this is the step where i need help*******************

run;

Kindly let me know if someone can help me with the iteration.

Re: iterate (through macro)

Tom — Tue, 21 Jul 2020 17:33:40 GMT

You don't seem to have provided all of the details and/or explained them clearly. What are the undefined macro variables your code is referencing? What do they have to do with the issue? You talk about min and max values and use a %DO loop from the min to the max but your example data does not have every value between the min and the max (there is no 2). Does that matter? Will that cause an issue?

First step is to figure out what SAS code you want to generate.

Then you can think about how to use macro code to generate it.

It sounds like SEP is an existing variable and you want to create TOTAL?

You could just use a series of IF statements:

if sep=0 then total=&s0;
if sep=1 then total=&s1;
....

That would be easy using your current %DO loop:

%do index=&min_var1 %to &max_var1 ;
  if sep=&index then total=&&s&index;
%end;

Re: iterate (through macro)

sahoositaram555 — Tue, 21 Jul 2020 17:55:00 GMT

Hi @Tom, sorry about not being clear. Bunch of if statements with sep will help, but that's the purpose of creating this macro as i'm unaware of the what'll come next(instaed of 0 ,1 ,3 ) may be 1,2,4). Well, thanks for recognizing the do loop.
As i'm not clear, let me clear that macro vars (s0,s1,s3) contains the individual category Counts from 3 diff products. i have to use them in a dataset named Surv where their child products are present in a column named Sep. so next to each child product their parent category counts must come.lets say if the child product(sep) is 0 then total( a new column) should get the value S0, similarly for child sep=1 then parent S1 value should be in total.Minima and maxima values created for the each of their use in loop as i dont want any other observation except the ones are in out_surv which may create problems such as after 0,1, i dont want 2 to come, need 3 to enter in to the loop.
My trap point is while the loop is running from minima to maxima it should smoothly assign the value irrespective of the inputs.

Re: iterate (through macro)

Tom — Tue, 21 Jul 2020 18:17:36 GMT

Main point is to not put the values into macro variables to begin with. Leave them in a dataset and then just join the two.

You could just eliminate the macro code and resolve the macro variable directly in SAS code.

total=symgetn(cats('s',sep));

If you want to put them into macro variables (and the list is short enough) then put them into one macro variable. Or in your case two macro variables, one with the counts and one with the category id value.

proc sql noprint;
select category,counts
  into :categories separated by '|'
     , :cat_counts separated by '|'
from category_counts
;
%let n_categories=&sqlobs;
quit;

Now you can loop from 1 to &N_CATEGORIES.

%do index=1 %to &n_categories;
if sep=%scan(&categories,&index,|) then total=%scan(&cat_counts,&index,|);
%end;

Re: iterate (through macro)

Reeza — Tue, 21 Jul 2020 18:23:18 GMT

Sounds like you're creating a Table1 for survival reporting?
https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030

Re: iterate (through macro)

smantha — Tue, 21 Jul 2020 20:50:03 GMT

proc sql;
select min(var),max(var) into :min_var1, :max_var1 
from out_surv;
quit;

data new_dataset;
set Surv;
          %do i=&min_var1 %to &max_var1;
                         if Sep=&i. then total=&&s&i..;
         %end;
run;

Is the above what you are looking for?

Re: iterate (through macro)

sahoositaram555 — Wed, 22 Jul 2020 04:14:36 GMT

Hi @smantha, I have this existing, the error comes while &&s&i resolves to S2 as it doesnot exist.
What i have is S0, S1,S3...that's why when the I resolves to 2 it shows below errors
ERROR 386-185: Expecting an arithmetic expression.
ERROR 200-322: The symbol is not recognized and will be ignored.

Re: iterate (through macro)

Kurt_Bremser — Wed, 22 Jul 2020 07:00:24 GMT

From your description, you do not need any macro coding at all (aside from the use of the predefined macro variables):

%let s0=111;
%let s1=222;
%let s3=333;

data surv;
input Sep;
datalines;
0
0
0
0
1
1
1
1
3
3
3
3
;

data want;
set surv;
total = symget(cats('s',sep));
run;

It looks like it would be best for you to forget (for the time being) that macros exist. This will force you to concentrate on the data/proc step code that really does the job. See Maxim 11.

Re: iterate (through macro)

sahoositaram555 — Wed, 22 Jul 2020 07:29:16 GMT

Thank you @Kurt_Bremser, it's a great piece of advice.

Re: iterate (through macro)

Kurt_Bremser — Wed, 22 Jul 2020 07:52:12 GMT

I guess you have those values for your macro variables somewhere in a dataset; if yes, creating a format from that dataset is even better in terms of code readability and performance:

data source;
input var value;
datalines;
0 111
1 222
3 333
;

data cntlin;
set source end=done;
fmtname = "lookup";
type = 'i';
rename
  var=start
  value=label
;
output;
if done
then do;
  hlo = 'O';
  value = .;
  output;
end;
run;

proc format cntlin=cntlin;
run;

data surv;
input Sep;
datalines;
0
0
0
0
1
1
1
1
2
3
3
3
3
;

data want;
set surv;
total = input(sep,lookup.);
run;

The INPUT() function will create a numeric result; if you want your result to be character, create a numeric format (type 'N') and use PUT().

The most advanced (and most performant) solution for a lookup is the use of hash objects:

data want;
set surv;
if _n_ = 1
then do;
  length var total 8;
  declare hash l (dataset:'source (rename=(value=total))');
  l.definekey('var');
  l.definedata('total');
  l.definedone();
end;
if l.find(key:sep) ne 0 then total = .;
run;

Bottom line: once you have created your solution and find that it has repeating code elements, or you want to make it dynamic with the use of parameters so you can apply it effortlessly to different situations, THEN you start to think about macros. NOT before that.