Comparing tables

[Xing]

Hi SAS experts,

Currently, I have two datasets. dataset1 has two columns, ID and svcdate, with duplicate ID. Dataset2 has two columns, ID and admdate, with no duplicates. I want to see whether the ID in dataset2 appears in dataset1. If it does appear, I want count how many times the admdate is bigger than svcdate for those IDs. How can I achieve this?

Here are two sample datasets:

data test1;
	input svcdate : mmddyy10. id @@;
	format svcdate mmddyy10.;
	cards;
04/11/2008 1 03/29/2008 1 07/22/2008 1
03/05/2008 2 11/15/2008 2 01/04/2008 2
01/07/2008 3 04/05/2008 3 06/06/2008 3
;
run;

data test2;
	input admdate : mmddyy10. id @@;
	format admdate mmddyy10.;
	cards;
05/01/2008 1
08/01/2008 2
07/01/2008 3
;
run;

proc sort data=test1;
	by id svcdate;
run;

proc sort data=test2;
	by id admdate;
run;

Thanks!

[ghosh]

data test3;
merge test1 test2(in=a);
 by id;
  if a;
 visits=admdate GT svcdate;
 if visits;
run;
proc summary nway;
class id;
 var visits;
  output out=totals (drop=_:)
  sum=;
 run;
 proc print label;
   label visits="admdate GT svcdate";
run;
 

[tsap · Accepted solution]

PROC SQL;
CREATE TABLE WORK.WANT	AS
	SELECT
		  a.ID
		, COUNT(a.ID)	AS Count_ADM_GT

	FROM		WORK.Test1	AS a
	INNER JOIN	WORK.Test2	as b	ON a.ID=b.ID
	WHERE b.ADMDATE > a.svcdate
	GROUP BY a.ID
	ORDER BY a.ID;
QUIT;

Results Generated:

id	Count_ADM_GT
1	2
2	2
3	3