tracking changes in a variable - long data

axescot78 — Wed, 13 Dec 2023 18:40:53 GMT

I have data by user ID that is receive on a monthly basis. There is one variable that can change, once or twice or more. I want to be create a flag for those individuals that have this variable changed and record that changes.

What I want:
UserID changed res1 res2 res3
1693      1               Y      M
1129      1                Y      N      M
1345      1               N     M      Y

What I have:

data fake_data;
input userID $ date date9. response $ ;
format date date9.;
datalines;
1693 01Dec2014 Y
1693 01Jan2015 Y
1693 01Feb2015 Y
1693 01Mar2015 M
1693 01Apr2015 M
1693 01May2015 M
1129 01Feb2018 Y
1129 01Mar2018 Y
1129 01Apr2018 N
1129 01May2018 N
1129 01Jun2018 M
1129 01Jul2018 M
1345 01Aug2016 N
1345 01Sep2016 N
1345 01Oct2016 N
1345 01Nov2016 N
1345 01Dec2016 M
1345 01Jan2017 M
1345 01Feb2017 Y
1345 01Mar2017 Y
1345 01Apr2017 Y
1345 01May2017 Y
;

Re: tracking changes in a variable - long data

PaigeMiller — Wed, 13 Dec 2023 19:14:06 GMT

This should work. I have changed the arrangement of the output data set to a format that works better in SAS (in other words, most SAS procedures need a long data set to work properly, whereas you wanted to create a wide data set).

data want;
    set fake_data;
    by userID notsorted;
    prev_response=lag(response);
    if first.userID then output;
    else if response^=prev_response then output;
    drop prev_response date;
run;

topic tracking changes in a variable - long data in SAS Programming

tracking changes in a variable - long data

Re: tracking changes in a variable - long data