topic Re: Trouble with do loop in SAS Procedures

Trouble with do loop

rkhachey — Tue, 12 Jan 2016 20:55:59 GMT

Hi, I'm a SAS novice so sorry if this is obvious.

I'm trying to find the first instance of a certain value for a variable. For example, this is some of my data&colon;

id grp mon v1 v2
1 1 0 0 86
1 1 1 0 86
1 1 2 0 23
1 1 3 0 23
1 1 4 0 23
2 3 0 86 86
2 3 1 86 16
2 3 2 86 16
2 3 3 16 20
2 3 4 16 20
3 2 0 0 86
3 2 1 0 86
3 2 2 0 86
3 2 3 0 16
3 2 4 0 20
4 3 0 1 86
4 3 1 3 28
4 3 2 2 28
4 3 3 2 28
4 3 4 2 24
5 3 0 2 86
5 3 1 3 16
5 3 2 4 24
5 3 3 4 24
5 3 4 3 16

The data set has the participants id, which group they were in, month data was collected, and two variables of interest, v1 and v2. For v1 and v2, I would like to be able to pull out the first value after 0 or 86. For example, for id 1, the value of v2 I am interested in is 23; for id 4 it is 28, etc.

I thought a round about way to do it would might be:

data check; set check;
if v1=86 then miss=.;
else if v1=0 then miss=.;
else do;
miss +1;
output;
end;
run;

Then I could filter for miss=1 and find my values of interest. The problem is that I do not know how to automatically repeat the code for each id.

Any help would be appreciated! Thank you!

Re: Trouble with do loop

Reeza — Tue, 12 Jan 2016 21:01:53 GMT

BY group processing - it's one of the best features of SAS programming.

Assuming this works for your cases, and that the data is sorted by ID this should help you get started. SAS will identify that it's a new ID and then you can reset or initialize any variables need.

data check;
set check;
BY ID;

if first.ID then do;
 *set variables to missing or reset as necessary;
end;

if v1=86 then miss=.;
else if v1=0 then miss=.;
else do;
miss +1;


output;
end;
run;

Re: Trouble with do loop

ballardw — Tue, 12 Jan 2016 21:06:08 GMT

Please provide an example of the desired output. Also include at least one example where V1 is used.

A key part is likely to be the function LAG which allows values on previous records in a dataset to be examined at the same time as the current row.

Something like;

Data want;

set have;

Lv1= Lag(v1);

Lv2 = Lag(v2);

if Lv2 in (86, 0) then output;

run;

Re: Trouble with do loop

Ksharp — Wed, 13 Jan 2016 01:15:59 GMT

Yes. Bring up the output you need.

Assuming ID variable has been sorted.

data have;
input id grp mon v1 v2;
cards;
1 1 0 0 86
1 1 1 0 86
1 1 2 0 23
1 1 3 0 23
1 1 4 0 23
2 3 0 86 86
2 3 1 86 16
2 3 2 86 16
2 3 3 16 20
2 3 4 16 20
3 2 0 0 86
3 2 1 0 86
3 2 2 0 86
3 2 3 0 16
3 2 4 0 20
4 3 0 1 86
4 3 1 3 28
4 3 2 2 28
4 3 3 2 28
4 3 4 2 24
5 3 0 2 86
5 3 1 3 16
5 3 2 4 24
5 3 3 4 24
5 3 4 3 16
;
run;
data want;
 set have;
 if id eq  lag(id) and v2 ne 86 and lag(v2)=86;
run;