BookmarkSubscribeRSS Feed
model_coder
Calcite | Level 5
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;
4 REPLIES 4
RickM
Fluorite | Level 6
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
Calcite | Level 5
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
SAS Super FREQ
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
Calcite | Level 5
Thanks Cynthia. I made it work with array. Message was edited by: model_coder

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