topic Re: How to categorize data using date variable in SAS Programming

How to categorize data using date variable

Rahim9 — Wed, 01 May 2019 22:45:58 GMT

Hi everyone,

I am a new SAS user, I have a dataset with several variables containing patient's information such as age, gender, clinical status, etc. I want to categorize my data into a 'before' and 'after' using a variable called 'date_current_status'. This 'date_current_status' is stored as a character variable. I first attempted to convert the variable into a SAS-date variable, then tried to use 'if then-else' statements to create the groups. However, when I tried to get the frequencies for the groups, I found that SAS has categorized all my data in one group.

This is the code I used:

/*CREATE NEW SAS DATE VARIABLE FROM THE DATE VARIABLES IN THE DATA*/

Data kano4;
set kano3;
format datevar date11.;
datevar = input (date_current_status, MMDDYY10.);
run;

/*check the above step*/

proc contents data=kano4;
run;

/*categorize study participants into before and after*/

Data groups;

set kano4;

length Group $15;

Date=datepart(DATEVAR);

if date > '30-SEP-2016'D and date <= '30-SEP-2017'D then

Group="BEFORE";

else if date >= '01-OCT-2017'D and date <= '01-OCT-2018'D then

Group= "AFTER";

else Group="Other";

proc freq data= groups nlevels;

table group /out=Groupdate outcum nopercent;

run;

Any help regarding this will be appreciated.

Re: How to categorize data using date variable

ballardw — Wed, 01 May 2019 22:57:06 GMT

It is best to provide actual example date of your data set.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Without seeing actual values it is hard to tell why things didn't work as expected

You may want to try using a custom format. The logic is pretty simple.

proc format library=work;
value beforeafter
'30SEP2016'd <- '20SEP2017'd ='Before'
'01OCT2017'd -  '01OCT2018'd ='After'
other= 'Other';


data example;
   do date = '15AUG2016'd to '20OCT2018'd;
      output;
   end;
run;

proc freq data=example;
   tables date;
   format date beforeafter.;
run;

The format approach is extremely flexible when coupled with date values as if you need a different grouping all you need is a different format and to use it as needed. The format will be honored by most analysis, reporting and graphic procedures.

Re: How to categorize data using date variable

PeterClemmensen — Wed, 01 May 2019 22:57:37 GMT

Hi and welcome to the SAS Communities 🙂

First of all, did the conversion to numeric date values succeed?

Secondly, this kind of binning problem is easier handled with a format. I can't see your data so the below code is untested. Let me know if it works for you

proc format; 
value dtfmt   '30sep2016'd <- '30sep2017'd = "Before" 
              '30sep2017'd <- '01oct2018'd = "After" 
               other                       = 'Other'; 
run;

proc freq data= groups nlevels;
   table group /out=Groupdate outcum nopercent;
   format group dtfmt.;
run;

Re: How to categorize data using date variable

Rahim9 — Wed, 01 May 2019 23:15:31 GMT

Hi,

Thank you for helping me out.

After attempting to do the numeric conversion, I used proc contents procedure and found that a new variable 'datevar' was created and the format was 'DATE11' This, I suppose indicates the conversion was a success.

However, when I used the code you provided, I get an error message with a message that "YOU ARE TRYING TO USE A NUMERIC FORMAT DTFM WITH A CHARACTER VARIABLE GROUP".

I am not sure I understand how to share my data in the forum for better communication.

Thanks

Re: How to categorize data using date variable

Tom — Wed, 01 May 2019 23:40:32 GMT

The format is defined to work on the variable with the actual date values, not the character variable GROUP.

Re: How to categorize data using date variable

Astounding — Thu, 02 May 2019 01:56:47 GMT

In your original program, this statement is incorrect and should be removed:

Date=datepart(DATEVAR);

DATEVAR is already a date variable. The DATEPART function is appropriate when your incoming variable is a DATETIME, not a DATE.

It is possible that just removing this statement will give you the results you are seeking. To be certain, we would need to see a few examples of the contents of DATEVAR and DATE_CURRENT_STATUS. Or you could just remove the offending statement and see if the results are to your liking. (Of course, the statements that follow would also need a slight adjustment. They would have to work with DATEVAR, instead of DATE.)