macro do loop help

[model_coder]

How can I use value of a variable within a dataset to create a macro do loop? My objective is to create a do loop within a data step where the end value for the do loop changes per row. For example, in the code below, I want to use upperlimit in the do loop.

%macro testing();
data test;
set mydata;
counter = 0;
upperlimit = ranuni(3271985);
%do i = 1 %to upperlimit;
counter = counter + 1;
%end;
run;
%mend;
%testing;

[RickM]

Is there a reason why you need to use a macro with %do instead of using the regular data step do loop? Also, ranuni gives a value from 0 to 1 so you probably want to change that.

[model_coder]

Rick,

I just used ranuni as an example. Actually in my code, I have column names that are numbered from 1 to 10. For example, col_1, col_2, col_3..col_10. For each row, we determine how many columns should feed into the sum. So for example, for row 1, we need to add 4 columns to compute a sum. Similarly, for second row, we may use 5 columns to calculate sum. I have updated the code to make logic little more cleaner.

%macro testing();
data test;
set mydata;
sum = 0;
column_count = func(some_logic);
%do i = 1 %to column_count ;
sum=sum+col_&i;
%end;
run;
%mend;
%testing; Message was edited by: model_coder

[Cynthia_sas]

Hi:
I agree with Rick. I'm not sure you need a do loop at all -- not a Macro %DO loop or a DATA step DO loop.

Consider the following program. It uses the SUM function to create the SUM1 variable, then uses the NMISS and N functions to see which of the 10 COL_x variables is missing or not.

As an alternative a DATA Step array is used with a regular DO loop to calculate the SUM2 variable. As you can see in the output, the result for SUM1 and SUM2 is the same.

If you need to know how many columns are "filled" and how many are "empty" or missing, the other SAS functions do that quite well -- see how the values for NUM_MISSING and NUM_THERE were calculated.

Here's the program and output.

cynthia
[pre]
** make some fake data;
data testdata;
infile datalines;
input cat $ col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10;
return;
datalines;
A 1 1 1 1 1 1 1 1 1 .
B 2 2 2 2 2 2 2 2 2 2
C 1 1 1 1 1 . . . . .
D 2 2 . . . . . . . .
;
run;

data calcsum;
set testdata;
array sd col_1-col_10;

** Approach 1: Use SUM function, which ignores missing values;
sum1 = sum (of col_1-col_10);
num_missing = nmiss(of col_1-col_10);
num_there = n(of col_1-col_10);

** Approach 2: use ARRAY with num_there variable value;
** to avoid missing values in calculation of SUM2 variable.;
sum2 = 0;
do i = 1 to num_there;
sum2 = sum2 + sd(i);
end;
label sum1 = 'Calc with SUM function'
sum2 = 'Calc with DATA step array';
run;

options nodate ls=120;
proc print data=calcsum label;
title 'Probably do not need an array for SUM2';
var cat col: num_missing num_there sum1 sum2;
run;
[/pre]


and here's the output:
[pre]
Calc with Calc with
num_ num_ SUM DATA step
Obs cat col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10 missing there function array

1 A 1 1 1 1 1 1 1 1 1 . 1 9 9 9
2 B 2 2 2 2 2 2 2 2 2 2 0 10 20 20
3 C 1 1 1 1 1 . . . . . 5 5 5 5
4 D 2 2 . . . . . . . . 8 2 4 4

[/pre]

[model_coder]

Thanks Cynthia. I made it work with array. Message was edited by: model_coder