Hi SAS experts,
Currently, I have two datasets and I want to count the number of visits for each id. If the id in date1 appeared in date2 and the admdate in date2 is within 1 year prior to the admdate in date1, then the number of visit should increase by 1. Any suggestions on sql?
Thanks!
data date1;
input admdate : mmddyy10. id @@;
format admdate mmddyy10.;
cards;
05/01/2009 1
08/01/2010 2
07/01/2011 3
08/24/2007 4
12/11/2012 5
;
run;
data date2;
input admdate : mmddyy10. id @@;
format admdate mmddyy10.;
cards;
03/02/2007 1 04/25/2008 1 06/01/2008 1
05/15/2007 2 08/21/2009 2 10/22/2008 2
06/17/2006 3
09/10/2006 4 09/30/2006 4
;
run;
Try this. I left it in 2 steps so you could see my thought process:
proc sql;
create table temp as
select date1.id, date1.admdate - date2.admdate as diff
from date1, date2
where date1.id=date2.id and date1.admdate - date2.admdate < 365
group by date1.id;
create table want as
select id, count(id) as count
from temp
group by id;
quit;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Try this. I left it in 2 steps so you could see my thought process:
proc sql;
create table temp as
select date1.id, date1.admdate - date2.admdate as diff
from date1, date2
where date1.id=date2.id and date1.admdate - date2.admdate < 365
group by date1.id;
create table want as
select id, count(id) as count
from temp
group by id;
quit;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
@Xing :
I would suggest:
data date1 ;
input id admdate :mmddyy10. ;
format admdate mmddyy10. ;
cards ;
1 05/01/2009
2 08/01/2010
3 07/01/2011
4 08/24/2007
5 12/11/2012
run ;
data date2 ;
input id admdate :mmddyy10. ;
format admdate mmddyy10. ;
cards ;
1 03/02/2007
1 04/25/2008
1 06/01/2008
2 05/15/2007
2 08/21/2009
2 10/22/2008
3 06/17/2006
4 09/10/2006
4 09/30/2006
run ;
proc sql ;
create table need as select date1.*, N_visits
from date1
left join
(select J.id, sum (ad2 <= intnx ("yr", ad1, -1, "s")) as N_visits
from (select date2.id, date1.admdate as ad1, date2.admdate as ad2
from date1 inner join date2 on date1.id = date2.id) J
group id) N
on date1.id = N.id
;
quit ;
Kind regards
Paul D.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.