SAS Do Loop: How to refer to an outside cell value

Question

I have two questions regarding Do Loops in SAS. Say that I have three datasets: (1) one dataset, called last with one observation called last_observation:

last_observation
150

(2) a second dataset with two columns of observations:

Time    ID
34200   1
34201   2
34210   3
34213   4
.       .
.       .
.       .
36000   150

Notice that my time increments increases randomly and has 925 observations. The same number found in my 1X1 dataset (1).

(3) A third dataset have with two columns of observations:

Purchases_unit Time
1000           34200
2000           34210
1243           34211
3040           34300
.              .
.              .
1000           36000

What I want to do is the following:

data _null_;
set last;
where last=last;
call symput('last_obs',last);run;

%Do i=1 to &'last_obs'; *Hence a loop for 1 to 150 IDs

data want_&i;
set have;
if time<time(i) then delete;
run;  
%end;
%mend;

The time(i) refers to the time column in the second dataset and the (i) is the cell subscript of the time column. So my two questions are:

(1) Will this work?: %Do i=1 to &'last_obs'; if I use the symput function? (2) How can I implement the time(i) index?

score 1 · Accepted Answer · answered Sep 03 '13 at 00:32

1

I would use a hash object to look up the value you want.

data last;
last_observation= 4;
call symput("last",last_observation);
run;

data time_id;
input Time    ID;
datalines;
34200   1
34201   2
34210   3
34213   4
;;;
run;

data purchases;
input Purchases_unit Time;
datalines;
1000           34200
2000           34210
1243           34211
3040           34300
;;;
run;

%macro loopit(n);
%do i=1 %to &n;
data want_&i(drop=rc);
set purchases;
format time_i best.;
retain time_i id;
if _n_ = 1 then do;
    declare hash lookup(dataset:"time_id(rename=(time=time_i))");
    rc = lookup.definekey("id");
    rc = lookup.definedata("time_i");
    rc = lookup.definedone();

    id = &i;

    rc = lookup.find();
end;

if time >= time_i;
run;
%end;
%mend;

%loopit(&last);

In the first observation, you look up the value from the look up table. Retain that value and use a subsetting if statement.

answered Sep 03 '13 at 00:32

DomPazz

12,415
17
23

Superb! Thanks for this hash code. This will be my first implementation of hash objects. – Plug4 Sep 03 '13 at 04:38
I have another question, how can I include an additional `IF` statement in a hash object? For example, beside `if time >= time_i;` I want to include `if time=0` so the observations that are greater or equal to `time_i` and those where `time=0`. How can I do so? I tried simply adding `if time=0` but I have zero observations left and that is because I can't have both `time=0` `time>=time_i` for a single observation. – Plug4 Sep 03 '13 at 06:46
1

try `if time>=time_i or time=0;` The subsetting if statement keeps all values that evaluate to TRUE. Just add any additional conditions on there. You can go back to your "if then delete;" too. – DomPazz Sep 03 '13 at 13:35
Ah I see where was my mistake in my implementation of the code. Again thanks a lot! – Plug4 Sep 03 '13 at 17:44

SAS Do Loop: How to refer to an outside cell value

1 Answers1