topic Re: How to get single row from multiple rows in SAS Programming

How to get single row from multiple rows

hammerman — Mon, 21 Dec 2015 07:25:50 GMT

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

Re: How to get single row from multiple rows

Jagadishkatam — Mon, 21 Dec 2015 07:53:55 GMT

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;

Re: How to get single row from multiple rows

Ksharp — Mon, 21 Dec 2015 08:27:39 GMT

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;

Re: How to get single row from multiple rows

hammerman — Tue, 22 Dec 2015 04:44:08 GMT

It works

Thanks Xia

Re: How to get single row from multiple rows

hammerman — Tue, 22 Dec 2015 04:46:22 GMT

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.