BookmarkSubscribeRSS Feed
Maiio
Calcite | Level 5

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:

Capture.PNG

 

Thanks in advance

5 REPLIES 5
sustagens
Pyrite | Level 9

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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
Opal | Level 21

Assuming your dates are imported as proper SAS dates :

 

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

PG
Tom
Super User Tom
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1480 views
  • 3 likes
  • 5 in conversation