topic Re: Grouping dates in SAS Programming

Grouping dates

Dhana18 — Thu, 30 Aug 2018 14:52:36 GMT

Why isn't this working"

data surrg.merged_date_cat;
set surrg.merged_date;
if date >= '01JAN2017'D and date <='31JAN2017'D then date_cat="Jan_mar";
if date >= '01APR2017'D and date <='01JUN2017'D then date_cat="Apr_Jun";
if date >= '01JUN2017'D and date <='30SEP2017'D then date_cat="Jul_Sep";
if date >= '09OCT2017'D and date <='31DEC2017'D then date_cat="Oct_Dec";
run;

Re: Grouping dates

PaigeMiller — Thu, 30 Aug 2018 15:00:08 GMT

Must be your data isn't allowing this to work. Please show us (a portion of) your actual data set.

Re: Grouping dates

novinosrin — Thu, 30 Aug 2018 15:03:31 GMT

Not sure. Can you post your log and few details, i just see one change '01JUL2017'D

Is you date numeric or char variable?

data surrg.merged_date_cat;
set surrg.merged_date;
if date >= '01JAN2017'D and date <='31JAN2017'D then date_cat="Jan_mar";
if date >= '01APR2017'D and date <='01JUN2017'D then date_cat="Apr_Jun";
if date >= '01JUL2017'D and date <='30SEP2017'D then date_cat="Jul_Sep";
if date >= '09OCT2017'D and date <='31DEC2017'D then date_cat="Oct_Dec";
run;

Re: Grouping dates

Dhana18 — Thu, 30 Aug 2018 15:08:31 GMT

PATIENT_ID

Date

210

3-Jan-17

210

3-Jan-17

210

21-Feb-17

210

14-Mar-17

210

14-Mar-17

210

3-Apr-17

210

11-May-17

210

11-May-17

210

14-Jun-17

210

18-Jul-17

210

14-Aug-17

210

14-Aug-17

210

6-Sep-17

210

12-Oct-17

210

16-Nov-17

210

15-Dec-17

210

15-Dec-17

Re: Grouping dates

Dhana18 — Thu, 30 Aug 2018 15:09:31 GMT

It is character.

Re: Grouping dates

PaigeMiller — Thu, 30 Aug 2018 15:12:05 GMT

You have to convert the dates to numeric for your code to work.

Something like

date_num = input(date,mmddyy8.);

Re: Grouping dates

novinosrin — Thu, 30 Aug 2018 15:16:30 GMT

Oh well in that case

try

if '01JAN2017'D<= input(date,date9.)<='31mar2017'D then date_cat="Jan_mar";

instead of

if date >= '01JAN2017'D and date <='31JAN2017'D then date_cat="Jan_mar";

Re: Grouping dates

Astounding — Thu, 30 Aug 2018 15:24:44 GMT

I can see three more:

data surrg.merged_date_cat;
set surrg.merged_date;
if date >= '01JAN2017'D and date <='31MAR2017'D then date_cat="Jan_mar";
if date >= '01APR2017'D and date <='30JUN2017'D then date_cat="Apr_Jun";
if date >= '01JUL2017'D and date <='30SEP2017'D then date_cat="Jul_Sep";
if date >= '01OCT2017'D and date <='31DEC2017'D then date_cat="Oct_Dec";
run;

For safety, I would change the structure of the statements.

data surrg.merged_date_cat;
set surrg.merged_date;

cutoff = date; /* if date is actually numeric */

cutoff = input(date, date9.); /* if date is actually character */

if cutoff >= '01JAN2018'D then return;
else if cutoff >= '01OCT2017'D then date_cat="Oct_Dec";

else if cutofff >= '01JUL2017'D then date_cat="Jul_Sep";

else if cutoff >= '01APR2017'D then date_cat="Apr_Jun";

else if cutoff >= '01JAN2017'D then date_cat="Jan_mar";

run;
run;

Re: Grouping dates

novinosrin — Thu, 30 Aug 2018 15:15:40 GMT

Thank you sir. I will have to step out for starb for strong caffeine before i post more. 🙂

Re: Grouping dates

ballardw — Thu, 30 Aug 2018 15:26:13 GMT

You might consider using the YYQ. format with the date variable as well.

Re: Grouping dates

Dhana18 — Thu, 30 Aug 2018 15:26:31 GMT

It worked! Thank you so much I really appreciate your help:)

Re: Grouping dates

Dhana18 — Thu, 30 Aug 2018 15:29:24 GMT

Thank you it worked!!!