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 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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