topic Re: how to create flags based on change from one record to other and no change in score from one rec in SAS Programming

how to create flags based on change from one record to other and no change in score from one record

noda6003 — Fri, 15 Mar 2019 13:06:52 GMT

I want to create 2 flags: one is if there is change of more than 5 points between recs and another flag if there is no change in score from recs to another 3 consecutive recs.

I am not figuring it out on how to do this and need help.

Required flag1 and flag2 and need something like below

PID	recs	score	flag1	flag2
121	1	11
121	2	23	Y
121	3	23		Y
121	4	23		Y
121	5	23		Y
122	1	11
122	2	14
122	3	24	Y
122	4	34	Y
122	5	22
122	6	11

Re: how to create flags based on change from one record to other and no change in score from one rec

VDD — Fri, 15 Mar 2019 13:17:43 GMT

in your want should this

122

be this

122

Re: how to create flags based on change from one record to other and no change in score from one rec

noda6003 — Fri, 15 Mar 2019 13:19:13 GMT

It should be as per pid.

Re: how to create flags based on change from one record to other and no change in score from one rec

PaigeMiller — Fri, 15 Mar 2019 13:21:55 GMT

/* UNTESTED CODE */
Data want;
    set have;
    Prev_id=lag(pid);
    Prev_id2=lag2(pid);
    prev_score=lag(score);
    prev_score2=lag2(score);
    if score-prev_score>5 and pid=prev_id then flag1='Y';
    if score=prev_score and score=prev_score2 
        and pid=prev_id and pid=prev_id2 then flag2='Y';
run;

Re: how to create flags based on change from one record to other and no change in score from one rec

noda6003 — Fri, 15 Mar 2019 13:36:13 GMT

to mention is somehow should work for improving and worsening scores. so in the below for 121 it has 9 and decreased the next recs to 2 and it needs to have flag. also if it has same score 3 consecutive times than the second 2 should have flags as Y- excluding the first

PID	recs	score	flag1	flag2
121	1	3
121	2	9	Y
121	3	2	Y
121	4	1
122	1	7
122	2	1	Y
122	3	3
122	4	3		Y
122	5	3		Y

Re: how to create flags based on change from one record to other and no change in score from one rec

PaigeMiller — Fri, 15 Mar 2019 13:44:30 GMT

to mention is somehow should work for improving and worsening scores. so in the below for 121 it has 9 and decreased the next recs to 2 and it needs to have flag.

This is a trivial change to the code I provided. Please see if you can make the modification to the code yourself.

also if it has same score 3 consecutive times than the second 2 should have flags as Y- excluding the first

Sort the data backwards, then if you get a Y in flag2, then add a Y to the next record.