DATA Step, Macro, Functions and more

Flag record based on previous value

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Flag record based on previous value

[ Edited ]

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;

 

 

 

 

 


Accepted Solutions
Solution
‎04-07-2017 12:31 AM
Super User
Posts: 11,343

Re: base sas

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).

View solution in original post


All Replies
Contributor
Posts: 66

Re: base sas

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.
 

Solution
‎04-07-2017 12:31 AM
Super User
Posts: 11,343

Re: base sas

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).

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 156 views
  • 0 likes
  • 2 in conversation