DATA Step, Macro, Functions and more

How to get single row from multiple rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How to get single row from multiple rows

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


Accepted Solutions
Solution
‎12-21-2015 11:43 PM
Super User
Posts: 10,046

Re: How to get single row from multiple rows

Posted in reply to hammerman

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


All Replies
Trusted Advisor
Posts: 1,137

Re: How to get single row from multiple rows

Posted in reply to hammerman

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
Occasional Contributor
Posts: 13

Re: How to get single row from multiple rows

Posted in reply to Jagadishkatam

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.

Solution
‎12-21-2015 11:43 PM
Super User
Posts: 10,046

Re: How to get single row from multiple rows

Posted in reply to hammerman

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;
Occasional Contributor
Posts: 13

Re: How to get single row from multiple rows

 It works

Thanks Xia

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 208 views
  • 1 like
  • 3 in conversation