BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AKHILA
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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;
AKHILA
Obsidian | Level 7

 

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.

Kurt_Bremser
Super User

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.

AKHILA
Obsidian | Level 7

thank u 🙂

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1134 views
  • 0 likes
  • 2 in conversation