assigning pre and post values

[rajd1]

Hello,

MY dataset has date difference (ADMIN – REPORT) variable for specific Gname. I want to pick the closest dates between the admin date and report date. For example, for ID#1, GName = ER, generic name  = Lap the date difference of +13.7 is closest so it goes under PostDiff. Similarly, date difference of -37.29 is closest on the negative side so this goes under prediff. Also, all unique Gnames by ID should be listed. Thanks a lot!

Data Have:

ID	GName	Report	Admin	Generic_name	Date_Diff
1	ER	9/8/2016	6/21/2016	Ado	-79.2919
1	ER	9/8/2016	5/31/2016	Ado	-100.292
1	ER	9/8/2016	7/12/2016	Ado	-58.2919
1	ER	9/8/2016	9/22/2016	Lap	13.70806
1	ER	9/8/2016	8/2/2016	Ado	-37.2919
1	ER	9/8/2016	10/11/2016	Lap	32.70806
1	MY	9/8/2016
1	NF	9/8/2016
1	TP	9/8/2016
2	EG	3/14/2017
2	ER	3/14/2017
2	ER	9/8/2016
2	ES	9/8/2016
2	ES	3/14/2017
2	MY	3/14/2017
2	MY	9/8/2016
2	NF	3/14/2017
2	NF	9/8/2016
2	PT	3/14/2017
3	CC	9/11/2016	8/25/2015	Pal	-383.8
3	CC	9/11/2016	11/9/2015	Pal	-307.8
3	ES	9/11/2016	5/26/2015	Ful	-474.8
3	ES	9/11/2016	7/26/2015	Ful	-413.8
3	ES	9/11/2016	1/19/2015	Ful	-601.8
3	ES	9/11/2016	6/23/2015	Ful	-446.8
3	ES	9/11/2016	3/24/2015	Ful	-537.8
3	ES	9/11/2016	4/28/2015	Ful	-502.8
3	ES	9/11/2016	2/22/2015	Ful	-567.8
3	FG1	9/11/2016
3	FG2	9/11/2016
3	FG3	9/11/2016
3	PI	9/11/2016	7/3/2017	Eve	294.1999
3	PI	9/11/2016	2/6/2017	Eve	147.1999

Data Want:

ID	Gname	Report	Generic_name	Admin	PreDiff	PostDiff
1	ER	9/8/2016	Ado	8/2/2016	37
1	ER	9/8/2016	Lap	9/22/2016		14
1	MY	9/8/2016
1	NF	9/8/2016
1	TP	9/8/2016
2	ER	9/8/2016
2	ES	9/8/2016
2	MY	9/8/2016
2	NF	9/8/2016
2	EG	3/14/2017
2	ER	3/14/2017
2	ES	3/14/2017
2	MY	3/14/2017
2	NF	3/14/2017
2	PT	3/14/2017
3	CC	9/11/2016	Pal	11/9/2015	308
3	ES	9/11/2016	Ful	7/26/2015	414
3	FG1	9/11/2016
3	FG2	9/11/2016
3	FG3	9/11/2016
3	PI	9/11/2016	Eve	2/6/2017		147

Extra Data Have:

ID	Gname	Report	Admin	Generic_name	Date_Diff
25	ER	9/14/2016	9/19/2017	Lap	369.2088
25	ER	9/14/2016	9/4/2013	Tra	-1106.79
25	ER	9/14/2016	5/27/2013	Tra	-1206.79
25	ER	9/14/2016	8/16/2016	Tra	-29.7912
25	ER	9/14/2016	2/19/2019	Lap	887.2088
25	ER	9/14/2016	8/7/2013	Tra	-1134.79
25	ER	9/14/2016	3/26/2013	Tra	-1268.79
25	ER	9/14/2016	11/7/2012	Tra	-1407.79
25	ER	9/14/2016	5/8/2013	Tra	-1225.79
25	ER	9/14/2016	10/27/2019	Ner	1137.209
25	ER	9/14/2016	4/17/2013	Tra	-1246.79
25	ER	9/14/2016	2/18/2019	Lap	886.2088
25	ER	9/14/2016	8/12/2014	Ado	-764.791
25	ER	9/14/2016	6/17/2013	Tra	-1185.79
25	ER	9/14/2016	11/27/2017	Lap	438.2088
25	ER	9/14/2016	1/21/2019	Lap	858.2088
25	ER	9/14/2016	1/15/2013	Tra	-1338.79
25	ER	9/14/2016	2/26/2013	Tra	-1296.79
25	ER	9/14/2016	7/8/2013	Tra	-1164.79
25	ER	9/14/2016	12/8/2013	Ado	-1011.79
25	ER	9/14/2016	6/28/2016	Tra	-78.7912
25	ER	9/14/2016	7/19/2016	Tra	-57.7912
25	ER	9/14/2016	12/18/2012	Tra	-1366.79
25	ER	9/14/2016	10/3/2016	Lap	18.20884
25	ER	9/14/2016	4/2/2019	Ner	929.2088
25	ER	9/14/2016	9/25/2012	Tra	-1450.79
25	ER	9/14/2016	1/30/2017	Lap	137.2088
25	ER	9/14/2016	4/26/2016	Tra	-141.791
25	ER	9/14/2016	4/17/2018	Lap	579.2088
25	ER	9/14/2016	5/21/2012	Tra	-1577.79
25	ER	9/14/2016	9/25/2013	Tra	-1085.79
25	ER	9/14/2016	7/2/2012	Tra	-1535.79
25	ER	9/14/2016	8/30/2017	Lap	349.2088
25	ER	9/14/2016	4/5/2016	Tra	-162.791
25	ER	9/14/2016	1/19/2016	Tra	-239.791
25	ER	9/14/2016	4/3/2012	Tra	-1625.79
25	ER	9/14/2016	12/1/2015	Tra	-288.791
25	ER	9/14/2016	11/10/2015	Tra	-309.791
25	ER	9/14/2016	9/5/2016	Lap	-9.79116
25	ER	9/14/2016	3/16/2016	Tra	-182.791
25	ER	9/14/2016	11/27/2012	Tra	-1387.79
25	ER	9/14/2016	6/11/2012	Tra	-1556.79
25	ER	9/14/2016	12/31/2013	Ado	-988.791
25	ER	9/14/2016	5/20/2014	Ado	-848.791
25	ER	9/14/2016	7/22/2014	Ado	-785.791
25	ER	9/14/2016	2/5/2013	Tra	-1317.79
25	ER	9/14/2016	8/13/2012	Tra	-1493.79
25	ER	9/14/2016	9/5/2012	Tra	-1470.79
25	ER	9/14/2016	12/22/2015	Tra	-267.791
25	ER	9/14/2016	10/23/2013	Tra	-1057.79
25	ER	9/14/2016	4/29/2014	Ado	-869.791
25	ER	9/14/2016	4/30/2012	Tra	-1598.79
25	ER	9/14/2016	2/18/2014	Ado	-939.791
25	ER	9/14/2016	4/5/2014	Ado	-893.791
25	ER	9/14/2016	5/17/2016	Tra	-120.791
25	ER	9/14/2016	5/13/2018	Lap	605.2088
25	ER	9/14/2016	7/23/2012	Tra	-1514.79
25	ER	9/14/2016	6/3/2019	Ner	991.2088
25	ER	9/14/2016	11/13/2013	Tra	-1036.79
25	ER	9/14/2016	10/26/2016	Lap	41.20884
25	ER	9/14/2016	10/16/2012	Tra	-1429.79
25	ER	9/14/2016	7/1/2014	Ado	-806.791
25	ER	9/14/2016	1/21/2014	Ado	-967.791
25	ER	9/14/2016	6/10/2014	Ado	-827.791
25	ER	9/14/2016	2/24/2016	Tra	-203.791
25	ER	9/14/2016	3/12/2014	Ado	-917.791
25	F1	9/14/2016
25	F2	9/14/2016
25	F3	9/14/2016

Extra Data Want:

ID	Gname	Report	Generic_name	Admin	PreDiff	PostDiff
25	ER	9/14/2016	Lap	9/5/2016	10
25	ER	9/14/2016	Lap	10/3/2016		18
25	F1	9/14/2016
25	F2	9/14/2016
25	F3	9/14/2016

[novinosrin]

Hi @rajd1  Assuming I understand your requirement and the data correctly, it's a straight forward SQL summary and an append aka union to get the full dataset-

data have;
input iD	GName $	(Report	Admin) (:mmddyy10.)	Generic_name $	Date_Diff;
format Report	Admin mmddyy10.;
cards;
1	ER	9/8/2016	6/21/2016	Ado	-79.2919
1	ER	9/8/2016	5/31/2016	Ado	-100.292
1	ER	9/8/2016	7/12/2016	Ado	-58.2919
1	ER	9/8/2016	9/22/2016	Lap	13.70806
1	ER	9/8/2016	8/2/2016	Ado	-37.2919
1	ER	9/8/2016	10/11/2016	Lap	32.70806
1	MY	9/8/2016	.	.	.
1	NF	9/8/2016	.	.	.
1	TP	9/8/2016	.	.	.
2	EG	3/14/2017	.	.	.
2	ER	3/14/2017	.	.	.
2	ER	9/8/2016	.	.	.
2	ES	9/8/2016	.	.	.
2	ES	3/14/2017	.	.	.
2	MY	3/14/2017	.	.	.
2	MY	9/8/2016	.	.	.
2	NF	3/14/2017	.	.	.
2	NF	9/8/2016	.	.	.
2	PT	3/14/2017	.	.	.
3	CC	9/11/2016	8/25/2015	Pal	-383.8
3	CC	9/11/2016	11/9/2015	Pal	-307.8
3	ES	9/11/2016	5/26/2015	Ful	-474.8
3	ES	9/11/2016	7/26/2015	Ful	-413.8
3	ES	9/11/2016	1/19/2015	Ful	-601.8
3	ES	9/11/2016	6/23/2015	Ful	-446.8
3	ES	9/11/2016	3/24/2015	Ful	-537.8
3	ES	9/11/2016	4/28/2015	Ful	-502.8
3	ES	9/11/2016	2/22/2015	Ful	-567.8
3	FG1	9/11/2016	.	.	.
3	FG2	9/11/2016	.	.	.
3	FG3	9/11/2016	.	.	.
3	PI	9/11/2016	7/3/2017	Eve	294.1999
3	PI	9/11/2016	2/6/2017	Eve	147.1999
;

proc sql;
 create table want(drop=date_diff) as
 select *, max(ifn(sign(date_diff)=-1,date_diff,.)) as PreDiff,
 min(ifn(sign(date_diff)=1,date_diff,.)) as PostDiff
 from have
 where date_diff>.
 group by id,gname,report, generic_name
 having PreDiff=Date_Diff or PostDiff=Date_Diff
 outer union corr
 select *
 from have
 where date_diff=.
 order by id,report,gname;
quit; 

[rajd1]

Hi @novinosrin,
Thanks so much for this. I have another example where this code didn't work. I have edited my original message and added extra data. Thanks so much

[novinosrin]

@rajd1  Your new have and want seem to have different values. Can you please review. Also, What is the logic to "subset"? Do you want only the records that have both post and pre ? Please explain further

[rajd1]

Hi @novinosrin,
I edited the data accordingly, sorry the formatting was off. So basically for the Gname ER, the closest Date difference for pre and post is what i need. For example in the new data, for the ER Gname the closest date difference for prediff is 9.79 and the postdiff is 18.20.
Using the previous code, it gives all the Date_difference by Generic name.

[novinosrin]

Okay @rajd1  Looks like you have almost answered your question. So did you try changing the

group by id,gname,report, generic_name

to

group by id,gname,report    and see what happens ?  🙂🙂

Believe me you are close, try that simple change and think before I or somebody else "type" it for you. 🙂

[rajd1]

@novinosrin,
I tried that and also changed the order by statement. Problem is that i still get both values in Prediff and Post diff 😞 😞

[novinosrin · Accepted solution]

proc sql;
 create table want(drop=date_diff _:) as
 select  *, max(ifn(sign(date_diff)=-1,date_diff,.))  as _PreDiff,
 ifn(calculated _PreDiff=date_diff,date_diff,.) as Prediff, 
 min(ifn(sign(date_diff)=1,date_diff,.)) as _PostDiff,
 ifn(calculated _PostDiff=date_diff,date_diff,.) as Postdiff
 from have
 where date_diff>.
 group by id,gname,report
 having PreDiff=Date_Diff or PostDiff=Date_Diff
 outer union corr
 select *
 from have
 where date_diff=.
 order by id,report,gname;
quit; 

iD	GName	Report	Admin	Generic_name	Prediff	Postdiff
25	ER	09/14/2016	09/05/2016	Lap	-9.79116	.
25	ER	09/14/2016	10/03/2016	Lap	.	18.2088
25	F1	09/14/2016	.		.	.
25	F2	09/14/2016	.		.	.
25	F3	09/14/2016	.		.	.

[rajd1]

Thank you sir!