I have a dataset like this
Id param vaL date1 date2
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 0APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
.......................................SO ON.....
I want to derive a flag variable , as per the conditions;
The flag should be populated for each param within each id;(only once as the conditions are satisfied for the firsttime)
Also date1 < date2 like
d param vaL date1 date2 FLAG
101 A 2.4 02jan2019 05jan2019 Y
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 0APR2019
101 B 8 04AUG2017 05SEP2017 Y
101 C 2 06FEB2016 01JAN2017
101 C 5 08MAR2014 08AUG2018 Y
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019 Y
.......................................SO ON.....
After making code out of your data, I found and fixed two typos in my code (supplying example data in usable form - data step with datalines - makes it easy for us to test code):
data have;
input
Id $ param $ val date1 :date9. date2 :date9.;
format date1 date2 yymmddd10.;
datalines;
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 05APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
;
data want;
set have;
by id param;
retain _flag;
if first.param then _flag = 0;
if date1 < date2 and not _flag
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
proc print data=want noobs;
run;
Result:
Id param val date1 date2 flag 101 A 2.4 2019-01-02 2019-01-05 Y 101 A 4.0 2019-01-05 2019-01-05 101 A 5.0 2019-01-10 2019-01-05 101 B 1.0 2018-05-03 2019-04-05 Y 101 B 8.0 2017-08-04 2017-09-05 101 C 2.0 2016-02-06 2016-01-01 101 C 5.0 2014-03-08 2018-08-08 Y 101 C 9.0 2017-04-07 2018-03-09 102 A 10.0 2016-09-09 2015-12-09 102 A 4.0 2019-01-05 2019-01-15 Y
Note that for id=101, param=B, the first observation satisfies the condition, as date1 is in 2018 and date2 in 2019.
This?
data want;
set have;
by id param;
retain _flag;
if first.param then flag = 0;
if date1 < date2 and not _flag;
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
hi,
I am not getting proper output. I want 3 flags for each id because there are 3 param values(A,B,C) as per the dataset.
After making code out of your data, I found and fixed two typos in my code (supplying example data in usable form - data step with datalines - makes it easy for us to test code):
data have;
input
Id $ param $ val date1 :date9. date2 :date9.;
format date1 date2 yymmddd10.;
datalines;
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 05APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
;
data want;
set have;
by id param;
retain _flag;
if first.param then _flag = 0;
if date1 < date2 and not _flag
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
proc print data=want noobs;
run;
Result:
Id param val date1 date2 flag 101 A 2.4 2019-01-02 2019-01-05 Y 101 A 4.0 2019-01-05 2019-01-05 101 A 5.0 2019-01-10 2019-01-05 101 B 1.0 2018-05-03 2019-04-05 Y 101 B 8.0 2017-08-04 2017-09-05 101 C 2.0 2016-02-06 2016-01-01 101 C 5.0 2014-03-08 2018-08-08 Y 101 C 9.0 2017-04-07 2018-03-09 102 A 10.0 2016-09-09 2015-12-09 102 A 4.0 2019-01-05 2019-01-15 Y
Note that for id=101, param=B, the first observation satisfies the condition, as date1 is in 2018 and date2 in 2019.
thank u 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.