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

Here is my sample set records which has parent_id and child_id

Parent_ID Benefit_Code Status_CODE Date_of_effect(DOE) CHILD_ID Entered_care(DOV)
1658 EGP ACT 20120926 1661 20060209
1658 EGP ACT 20120926 1661 20060209
1658 EGP ACT 20120926 1661 20120224
1658 EGP ACT 20120926 1661 20120224

 

Dataset is sorted by child_id and DOV

 

Conditions for get a single row are

1) Child first entered care, I am using the following logic IF first.child_id then output date first_entered_Care and it works.

2) If child entered care Gt 20080906 then (add new column as entered_care_after_200809 = 'Y') and 

3) If Child has multiple events after 20080906 then, output first occurence after 20080906

 

SO final output should look like

Parent_ID Benefit_Code Status_CODE Date_of_effect(DOE) CHILD_ID Date_of_variation(DOV) entered_care_after_200809 first_entered_Care date_entered_post 200809
1658 EGP ACT 20120926 1661 20120224 Y 20060209 20120224

 

How can include all three conditions in the same do loop

Any help would be appreciated

thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

So Parent_ID Benefit_Code  Status_CODE  Date_of_effect  are all the same with the same Child_ID ?

 

 

 

data have;
infile cards expandtabs truncover;
input Parent_ID 	Benefit_Code$ 	Status_CODE$ 	Date_of_effect :yymmdd8. 	CHILD_ID 	Entered_care : yymmdd8.;
format Date_of_effect Entered_care date9.;
cards;
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20120224
1658 	EGP 	ACT 	20120926 	1661 	20120224
;

data want;
set have;
by CHILD_ID ;
length entered_care_after_200809  $ 1;
retain entered_care_after_200809  first_entered_Care date_entered_post_200809;
if first.CHILD_ID then do;
 first_entered_Care=Entered_care;
 n=0;
 call missing(entered_care_after_200809,date_entered_post_200809);
end;
if Entered_care gt '06sep2008'd then do;
 entered_care_after_200809='Y';
 n+1;
 if n eq 1 then date_entered_post_200809=Entered_care ;
end;
if last.CHILD_ID then output;
format first_entered_Care date_entered_post_200809 date9.;
drop n;
run;

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

Please try

 

data have;
input Parent_ID 	Benefit_Code$ 	Status_CODE$ 	Date_of_effect :yymmdd8. 	CHILD_ID 	Entered_care : yymmdd8.;
format Date_of_effect Entered_care date9.;
cards;
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20120224
1658 	EGP 	ACT 	20120926 	1661 	20120224
;

data want;
set have;
by Parent_ID Entered_care;
retain first_entered_Care;
if first.parent_id then first_entered_Care=Entered_care;
if  Entered_care > '06sep2008'd then do;
entered_care_after_200809 = 'Y';
date_entered_post_200809=Date_of_effect;
end;
format first_entered_Care date_entered_post_200809 date9.;
if last.parent_id and entered_care_after_200809 = 'Y';
run;
Thanks,
Jag
hammerman
Obsidian | Level 7

Hi Jagadish,

 

with your solution it assign all the rows with 'Y' for entered_care_after_200809, even when the dates are not past the 20080906 date.

Ksharp
Super User

So Parent_ID Benefit_Code  Status_CODE  Date_of_effect  are all the same with the same Child_ID ?

 

 

 

data have;
infile cards expandtabs truncover;
input Parent_ID 	Benefit_Code$ 	Status_CODE$ 	Date_of_effect :yymmdd8. 	CHILD_ID 	Entered_care : yymmdd8.;
format Date_of_effect Entered_care date9.;
cards;
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20060209
1658 	EGP 	ACT 	20120926 	1661 	20120224
1658 	EGP 	ACT 	20120926 	1661 	20120224
;

data want;
set have;
by CHILD_ID ;
length entered_care_after_200809  $ 1;
retain entered_care_after_200809  first_entered_Care date_entered_post_200809;
if first.CHILD_ID then do;
 first_entered_Care=Entered_care;
 n=0;
 call missing(entered_care_after_200809,date_entered_post_200809);
end;
if Entered_care gt '06sep2008'd then do;
 entered_care_after_200809='Y';
 n+1;
 if n eq 1 then date_entered_post_200809=Entered_care ;
end;
if last.CHILD_ID then output;
format first_entered_Care date_entered_post_200809 date9.;
drop n;
run;
hammerman
Obsidian | Level 7

 It works

Thanks Xia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 863 views
  • 1 like
  • 3 in conversation