Dear All
My data set is as follows
Date ID VARA
01JAN2019 1 1
02Jan2019 1 1
03JAN2019 1 0
08JAN2019 1 1
22JAN2019 1 1
23JAN2019 1 1
05FEB2019 1 0
22FEB2019 1 0
25FEB2019 1 0
15JAN2019 2 1
24JAN2019 2 0
29JAN2019 2 0
12FEB2019 2 1
15FEB2019 2 0
The data I want is
Date ID VARA LO_A
01JAN2019 1 1 0
02Jan2019 1 1 1
03JAN2019 1 0 1
08JAN2019 1 1 0
22JAN2019 1 1 0
23JAN2019 1 1 1
05FEB2019 1 0 0
22FEB2019 1 0 0
25FEB2019 1 0 1
15JAN2019 2 1 1
24JAN2019 2 0 0
29JAN2019 2 0 1
12FEB2019 2 1 1
15FEB2019 2 0 1
So for each ID I want to flag the last observation of VARA
The data I wrote was
Data have; set have;
LO_A = 0;
run;
proc sort data = have; by ID date VAR_A ; run;
data want ; set have ;
by ID date VAR_A ;
if last.VAR_A then LO_A = 1 ;
run;
There is some mistake I am making in the logic of the sort.
Please help.
Randy
Hi @RandyStan
You can use the NOTSORTED option in the BY statement to specify that the rows are grouped by the BY variables, but are not presented in a sorted order. Anytime anyone of the BY variables changes value, SAS interprets this as a new BY group.
Best,
data want;
set have;
by ID VARA notsorted;
if last.VARA then LO_A=1;
else LO_A=0;
run;
Why not just
data have;
input Date :date9. ID VAR_A;
format Date date9.;
datalines;
01JAN2019 1 1
02Jan2019 1 1
03JAN2019 1 0
08JAN2019 1 1
22JAN2019 1 1
23JAN2019 1 1
05FEB2019 1 0
22FEB2019 1 0
25FEB2019 1 0
15JAN2019 2 1
24JAN2019 2 0
29JAN2019 2 0
12FEB2019 2 1
15FEB2019 2 0
;
data want;
set have;
by VAR_A notsorted;
LO_A = last.VAR_A;
run;
Result:
Date ID VAR_A LO_A 01JAN2019 1 1 0 02JAN2019 1 1 1 03JAN2019 1 0 1 08JAN2019 1 1 0 22JAN2019 1 1 0 23JAN2019 1 1 1 05FEB2019 1 0 0 22FEB2019 1 0 0 25FEB2019 1 0 1 15JAN2019 2 1 1 24JAN2019 2 0 0 29JAN2019 2 0 1 12FEB2019 2 1 1 15FEB2019 2 0 1
Hi @RandyStan
You can use the NOTSORTED option in the BY statement to specify that the rows are grouped by the BY variables, but are not presented in a sorted order. Anytime anyone of the BY variables changes value, SAS interprets this as a new BY group.
Best,
data want;
set have;
by ID VARA notsorted;
if last.VARA then LO_A=1;
else LO_A=0;
run;
Thank you. It works
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.