Flag record based on previous value

[molla]

Hi,

The below  data contains a flag variable whose values are depends on score , if the score value is less than the above score value then flag =0 otherwise if score greater  than above score then flag=1. Write a code to get output as above.

Obs        subject                 score     flag         YEAR

1              A                             23           0              92

2              A                             40           1              93

3              A                             26           0              94

4              B                             35           1              94

5              B                             46           1              95

6              B                             45           0              96

7              C                             50           1              94

8              C                             42           0              95

9              C                            37           0              95

I have done upto  the follwing am unable to  get 

A     23   0

on the top,pls help me .

data test1;
set test(firstobs=2);
run;

data test2;
set test(rename=(sub=_sub score=_score)firstobs=1 obs=8);
run;

data test3;
merge test1 test2;
if score le _score then flag=0;
else flag=1;
drop _sub _score;
run;

[molla]

Hi I have done above question in the following way,

 
data test1;
set test(firstobs=2);
run;

data test2;
set test(rename=(sub=_sub score=_score)firstobs=1 obs=8);
run;

data test3;
merge test1 test2;
if score le _score then flag=0;
else flag=1;
drop _sub _score;
run;



data test4;
set test;
if _N_=1 then output test4;
run;

data final;
set test4 test3;
run;

Bt it seems to be lengthy ,pls suggest me any methods to reduce code.
 

[ballardw · Accepted solution]

Please consider:

data have;
   input subject $ score  YEAR;
datalines;
A 23   92
A 40   93
A 26   94
B 35   94
B 46   95
B 45   96
C 50   94
C 42   95
C 37   95
;
run;

data want;
   set have;
   flag= (dif(score)>0);
run;

There are two functions in the SAS datastep that look at previous records LAG and DIF. DIF gives you the difference from the current value of the named variable with the previous. Note that these functions are actual Lag1 to LAGn and Dif1 to Difn where n represents how many rows previous you want to look.

The above code uses the SAS result of a comparison of true is assigned a value of 1 and 0 otherwise. so (dif(score)>0) returns 1 when the previous record value is lower than the current.

The first record does not have anything to compare to and so the comparison is false (missing is not > 0).