topic Re: data cleaning based on date in SAS Programming

data cleaning based on date

Xing — Mon, 24 Jun 2019 16:16:13 GMT

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;

Re: data cleaning based on date

noling — Mon, 24 Jun 2019 16:47:21 GMT

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;

Re: data cleaning based on date

hashman — Mon, 24 Jun 2019 17:29:26 GMT

@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.