topic Re: Identifing date of encounters in SAS Programming

Re: Identifing date of encounters

wanijsena — Mon, 10 Apr 2023 19:37:37 GMT

I'm working with a dateset that has a start date and an end date for certain events. The format for both date fields is YYMMDDN8. I'm using the following lines of code to generate a count of days from the start date to the end date:

proc sql;
create table dataset2
as select distinct *, 
intck('day', datestartrole, dateendrole) as roletime "Time at Role"
from dataset1;
quit;

This works perfectly for most observations; however some observations of dateendrole are coded C for "current" even though dateendrole is in the YYMMDDN8. format. My code returns a missing value for those observations. I tried to modify by using the following code:

proc sql;
create table dataset2 
as select distinct *,
case when (dateendrole = 'C') then intck('day', datestartrole, today()) else intck('day', datestartrole, dateendrole) end as roletime "Time at Role"
from dataset1;
quit;

However, this returns an error stating "Expression using equals (=) has components that are of different data types." So, even though the date field is coded as a C, the case when statement is not recognizing C as a valid value because dateendrole is a date field. Do you guys have any ideas on how to get around this?

Re: Identifing date of encounters

Reeza — Mon, 10 Apr 2023 19:50:20 GMT

I've moved your question to it's own post as it doesn't relate to post where it was located.

To answer your question, please run a proc contents on your 'dataset1' and include the type and format on your variable dateendrole. It is possibly numeric with a character format so it's not really a C.

EDIT: Actually, even in your question you treat it as a date and character variable at the same time. I suspect you're referring to the incorrect variable somewhere.

Character:

case when (dateendrole = 'C')

Numeric/Date

 intck('day', datestartrole, dateendrole)

@wanijsena wrote:
I'm working with a dateset that has a start date and an end date for certain events. The format for both date fields is YYMMDDN8. I'm using the following lines of code to generate a count of days from the start date to the end date:
proc sql;
create table dataset2
as select distinct *, 
intck('day', datestartrole, dateendrole) as roletime "Time at Role"
from dataset1;
quit;
This works perfectly for most observations; however some observations of dateendrole are coded C for "current" even though dateendrole is in the YYMMDDN8. format. My code returns a missing value for those observations. I tried to modify by using the following code:
proc sql;
create table dataset2 
as select distinct *,
case when (dateendrole = 'C') then intck('day', datestartrole, today()) else intck('day', datestartrole, dateendrole) end as roletime "Time at Role"
from dataset1;
quit;
However, this returns an error stating "Expression using equals (=) has components that are of different data types." So, even though the date field is coded as a C, the case when statement is not recognizing C as a valid value because dateendrole is a date field. Do you guys have any ideas on how to get around this?

Re: Identifing date of encounters

Kurt_Bremser — Mon, 10 Apr 2023 20:25:38 GMT

Are you sure the value you see is

and not

.C

<dot>C is a "special missing value". If you want to check for it, use the MISSING function.

Also, it is not necessary to use INTCK with an interval of "day"; SAS dates are counts of days, so a simple subtraction will do.