topic Subsetting dataset using WHERE and BETWEEN in SAS Procedures

Subsetting dataset using WHERE and BETWEEN

Mogamo_Khushua — Thu, 28 Dec 2017 22:35:42 GMT

I am combine two data sets based on a patient's date of surgery falling between +/- 1 month of when a patient completed their survey.

dataset abc has variables lastname, firstname, dob, and survey_completed

dataset xyz has variables last, first, date_birth, and date_surgery

Here's my code:

proc sql;
create table want as
select distinct t1.*,
intnx('month', t1.Survey_Completed, -1, 'sameday') as first_survey format=date9.,
intnx('month', t1.survey_completed, 1, 'sameday') as last_survey format=date9.,
t2.last,
t2.first,
t2.date_birth,
t2.date_surgery as DOS format date9.
from abc as t1
left join xyz as t2
on t1.dob=t2.date_birth and t1.last_name=t2.last and t1.first_name=t2.first
where DOS between first_survey and last_survey
order by last_name;
quit;

with the above code I am getting the following error message:

ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: first_survey, last_survey.

What am i doing wrong?

Re: Subsetting dataset using WHERE and BETWEEN

Reeza — Thu, 28 Dec 2017 22:44:36 GMT

t2.date_surgery as DOS format date9.  <- missing the equal sign here

What's the type/format on date_surgery before the query? I suspect it's a character variable and you'd need to convert it to a numeric variable and a SAS date first.

Re: Subsetting dataset using WHERE and BETWEEN

Mogamo_Khushua — Thu, 28 Dec 2017 22:54:26 GMT

type is numeric and format is date9.

Re: Subsetting dataset using WHERE and BETWEEN

PGStats — Thu, 28 Dec 2017 22:54:28 GMT

You must use the calculated keyword when referring to a calculated column.

where DOS between calculated first_survey and calculated last_survey

Re: Subsetting dataset using WHERE and BETWEEN

Mogamo_Khushua — Thu, 28 Dec 2017 22:58:07 GMT

It works after using 'calculated'.

Thank you very much.