topic Re: How to add missing dates in a dataset in SAS Programming

How to add missing dates in a dataset

sas_student1 — Fri, 23 Dec 2022 00:48:10 GMT

Hello,

I have tried to google this but am getting every other kind of solution but not to what I need, so am Hoping someone will be able to assist.

I have a dataset that has Hospital Name, client ID, date of treatment and a flag. if there is no date of treatment for a client in a hospital then there is no row with the date. What i want to do is create a dataset that has a column that is consecutive dates for the full month and then the flag would be 1 or 0 for if there was a treatment.

What I have is a table like this:

Hospital_name	Client_Id	Date	Flag
A	Tom	1/1/2022	1
A	Tom	1/2/2022	1
A	John	3/1/2022	1
A	John	3/10/2022	1
B	John	3/11/2022	1
B	Joe	1/5/2022	1
B	Joe	1/10/2022	1
B	John	5/1/2022	1
C	Sam	4/1/2022	1
C	Sam	4/10/2022	1

What I would like is to do is add in the missing dates for each client for the whole month. So for example Tom was seen on 1/1/2022 and 1/2/2022 he has no visits for the remaining of January but I would like to add rows for Jan 3 to 31 where the flag=0 instead of 1. Joe in Hospital B was seen on 1/5/2022 and then again on 1/10/2022, but there are multiple dates in Jan for Joe he was not seen I would like to add the full month of Jan but out a flag=0 for those months he was not seen. Then do the same of the other clients by the Hospital.

Here is the sas code to create the sample dataset:

data have;
input Hospital_name $1.	Client_Id $5. Date Flag $1.;
informat Date mmddyy10.;
format Date mmddyy10.;
cards;
A Tom 1/1/2022 1
A Tom 1/2/2022 1
A John 3/1/2022 1
A John 3/10/2022 1
B John 3/11/2022 1
B Joe 1/5/2022 1
B Joe 1/10/2022 1
B John 5/1/2022 1
C Sam 4/1/2022 1
C Sam 4/10/2022 1
;
run;

I'm sure the solution is simple I am just not getting it.

Thanks!

Re: How to add missing dates in a dataset

SK_11 — Fri, 23 Dec 2022 04:05:09 GMT

If I understand correctly you need for each client same date range and if there is a visit then the flag will be 1 else 0.


data dates;
%let startDate='01Jan2022'd;
%let endDate='31Dec2022'd;
do date=&startdate to &enddate;
output;
end;
format date mmddyy10.;
run;


Proc sql;
Create table want as
	Select 
	a.date
	, b.Hospital_name
	, a.Client_Id
	, case when b.date then 1 
		else 0
	end as flag
	from (Select *, Client_Id from dates cross join (Select distinct Client_Id from have) ) as a
	left join have as b
	on a.date=b.date and a.Client_Id=b.Client_Id
	order by a.Client_Id,  a.date
;Quit;
Sample Output:

date	Hospital_name	Client_Id	flag
1/01/2022	A	Tom	1
1/02/2022	A	Tom	1
1/03/2022		Tom	0
1/04/2022		Tom	0
1/05/2022		Tom	0
1/06/2022		Tom	0
1/07/2022		Tom	0
1/08/2022		Tom	0
1/09/2022		Tom	0
1/10/2022		Tom	0
1/11/2022		Tom	0
1/12/2022		Tom	0
01/13/2022		Tom	0

Re: How to add missing dates in a dataset

Ksharp — Fri, 23 Dec 2022 11:57:00 GMT

data have;
input Hospital_name $1. Client_Id $5. Date Flag $1.;
informat Date mmddyy10.;
format Date mmddyy10.;
cards;
A Tom 1/1/2022 1
A Tom 1/2/2022 1
A John 3/1/2022 1
A John 3/10/2022 1
B John 3/11/2022 1
B Joe 1/5/2022 1
B Joe 1/10/2022 1
B John 5/1/2022 1
C Sam 4/1/2022 1
C Sam 4/10/2022 1
;
run;
proc sql;
create table temp as
select distinct Hospital_name,Client_Id,year(Date) as year,month(Date) as month
 from have;
quit;
data temp2;
 set temp;
 do date=mdy(month,1,year) to intnx('month',mdy(month,1,year),0,'e');
   output;
 end;
 format date mmddyy10.;
 drop year month;
run;
proc sql;
create table want as
select a.*,coalescec(b.flag,'0') as flag
 from temp2 as a natural left join have as b;
quit;

Re: How to add missing dates in a dataset

sas_student1 — Fri, 23 Dec 2022 14:57:04 GMT

@Ksharpthanks that worked! Question: have not seen natural left join before. What does the "natural" mean?

Re: How to add missing dates in a dataset

Ksharp — Sat, 24 Dec 2022 08:35:12 GMT

That is the same with

from temp2 as a left join have as b
on a.Hospital_name=b.Hospital_name and a.Client_Id=b.Client_Id and a.Date=b.Date ;

that would be streamline, no need to type all the variables in common within two tables. Save your time ,doesn't it ?