PROC SQL in SAS date difference

Question

I have dates from two different datasets and I want to find the difference between the two dates such that only those with difference under 20 is included in the PROC SQL table. However, I am getting errors.

Dataset 1 = s_cases (has one date variable date_1)

Dataset 2 = q_Cases (has another date variable date_2)

Also, in the code below I have done a case when step to get info on some additional cases, you can ignore that part.

proc sql; 

create table cases_data as select
 
sum(case when " p ID"n in (select "p ID"n from s_cases where "p ID"n ne '') then 1 else 0 end) as additional_case
from q_case

where intck('day',s_case.date1, q_case.date2)<20;
 

quit;

Log errors:

ERROR: Unresolved reference to table/correlation name s_cases. ERROR: Function INTCK requires a numeric expression as argument 2. ERROR: Expression using less than (<) has components that are of different data types.

Extra note: the two variables are already in a numeric format, I do not know why SAS is asking for a numeric expression as argument 2.

Your first error is the issue - unresolved reference to table/correlation name s_cases. You do not join with the s_cases data set. You do use in inline in the case statement but that doesn't join the data set in so you cannot use any other variables from it unless you join it to the q_cases table. — Reeza
– Reeza, Commented Jul 19, 2022 at 22:05
Will one of the tables contain only one row per pID? If not you will get potentially elevated counts due to cross joining. — Richard
– Richard, Commented Jul 19, 2022 at 23:05

Tom · Accepted Answer · 2022-07-19 22:58:33Z

1

I think you want something like this, but just a guess. Post sample data for more help.

proc sql;
create table cases_data as
select * 
from q_case as q
inner_join s_case as s
on q.'p ID'n = s.'p ID'n
and not missing(q.'p ID'n) and not missing(s.'p ID'n)
where intck('day', s.date1, q.date2) < 20;
quit;

Then for the count you can do a select * but I'd check it first.

proc sql;
create table cases_data as
select count(s.'p ID'n) as additional_case 
from q_case as q
inner_join s_case as s
on q.'p ID'n = s.'p ID'n
and not missing(q.'p ID'n) and not missing(s.'p ID'n)
where intck('day', s.date1, q.date2) < 20;
quit;

This should return a single value which is the number of cases where the data difference is less than 20 and the ID is in both tables and not missing.

edited Jul 19, 2022 at 22:58

Tom

52.3k2 gold badges19 silver badges36 bronze badges

answered Jul 19, 2022 at 22:09

Reeza

21.4k4 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PROC SQL in SAS date difference

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related