topic Calculate number of days from more than one variable in SAS Programming

Calculate number of days from more than one variable

Maiio — Thu, 28 Nov 2019 00:51:48 GMT

I want to create a new variable that calculates the number of days between hospital admission date (adate) and the last time a person received a vaccine (vdate) --vaccination date being before admission date.

The problem is that a person can have up to 12 vaccinations, so vdate is actually 12 variables: from vdate_1 up to vdate_12 (arranged from oldest to newest).

I want to calculate the number of days based on each individual's LAST vaccination date regardless the number of vaccines that person took.

This is what the excel sheet looks like:

Thanks in advance

Re: Calculate number of days from more than one variable

sustagens — Thu, 28 Nov 2019 02:49:00 GMT

Here you go, I did it with cascading conditions

proc sql;
create table want as 
select t1.*,
(case when t1.vdate_12 not is null then t1.vdate_12 
	else ( case when t1.vdate_11 not is null then t1.vdate_11 
		else ( case when t1.vdate_10 not is null then t1.vdate_10
			else ( case when t1.vdate_9 not is null then t1.vdate_9
				else ( case when t1.vdate_8 not is null then t1.vdate_8
					else ( case when t1.vdate_7 not is null then t1.vdate_7
						else ( case when t1.vdate_6 not is null then t1.vdate_6
							else ( case when t1.vdate_5 not is null then t1.vdate_5
								else ( case when t1.vdate_4 not is null then t1.vdate_4
									else ( case when t1.vdate_3 not is null then t1.vdate_3
										else ( case when t1.vdate_2 not is null then t1.vdate_2
											else ( case when t1.vdate_1 not is null then t1.vdate_1 else . end)
											   end) end) end) end) end) end) end) end) end) end) end) as Last_Vaccine_Date format=date11.,
(t1.ADate-(calculated Last_Vaccine_Date)) as Day_Count
from have t1;
quit;

Next time please post your data as code, instructions here. This helps the person helping you.

Re: Calculate number of days from more than one variable

PGStats — Thu, 28 Nov 2019 05:01:39 GMT

Assuming your dates are imported as proper SAS dates :

days = intck("day", adate, max(of vdate_1-vdate_12));

Re: Calculate number of days from more than one variable

gamotte — Thu, 28 Nov 2019 16:16:05 GMT

Hello,

You don't need to add an else clause after each when. You can chain the when as follows :

           case when t1.vdate_12 not is null then t1.vdate_12
                when t1.vdate_11 not is null then t1.vdate_11
                ...
                when t1.vdate_1 not is null then t1.vdate_1 
           else . end as Last_Vaccine_Date format=date11.

=> Each when is evaluated only if the predeeding conditions have not been satisfied yet.

The coalesce function can alos be used here for a more concise expression :

 select t1.*, coalesce(t1.vdate_12, t1.vdate_11, ..., t1.vdate_1) as ...

Re: Calculate number of days from more than one variable

Tom — Thu, 28 Nov 2019 16:27:31 GMT

Why would you post a photograph of data? This forum's editor supports copy and paste of text. You can even paste in text from Excel.

So is the rule this:

find the max vaccination date that is less than (or equal to?) admission date and then take the difference?

Assuming the VDATE_xxx values are in order like you photograph seems to indicate you could use something like this:

data want ;
  set have;
  array vdates vdate_1-vdate_12 ;
  do index=1 to dim(vdates) while (not missing(vdates[index] and vdates[index] < adate);
     days = adate - vdates[index];
  end;
run;

Re: Calculate number of days from more than one variable

gamotte — Thu, 28 Nov 2019 16:27:54 GMT

The coalesce function is also avaliable in data steps :

data have;
    format vdate_1-vdate_12 last_non_null_date ddmmyy10.;
    vdate_1='11May2009'd;
    vdate_2='08Aug2006'd;
    vdate_3='02Dec2006'd;

    array dt vdate_12-vdate_1;

    last_non_null_date=coalesce(of dt(*));
run;