Calculate number of days from more than one variable

[Maiio]

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

[sustagens]

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.

[gamotte]

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

[gamotte]

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;

[PGStats]

Assuming your dates are imported as proper SAS dates :

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

[Tom]

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;