topic Re: Grouping the data by month in SAS Programming

Grouping the data by month

Ranjeeta — Fri, 15 Feb 2019 20:05:50 GMT

How would i write an if condition to group cases by the admission date for the 12 months in a year

ADM_DT field is in Numeric DATETIME20 format

i.e. I want to count the no of cases where ADM_DT would fall e.g. between 01 Jan and 31 Jan 2018

Re: Grouping the data by month

novinosrin — Fri, 15 Feb 2019 20:09:31 GMT

1. Please provide us a comprehensive sample of data HAVE

2. Please provide us a comprehensive sample of data WANT

3. Explain briefly what you want to accomplish

SAS family would provide you the solution 🙂

Re: Grouping the data by month

Ranjeeta — Fri, 15 Feb 2019 20:16:19 GMT

Data I Have

KEY 5482 5253 6942

ADM_DT 29JUN2018:00:00:00 09JUN2018:00:00:00 15JUL2018:00:00:00

DATA WANT

JUN2018 2 Keys

JULY2018 1KEY

Basically I want to count the no of cases that are there in each month in the above e.g 2 keys were between the dates of 01 June 2018 to 30 June 2018

Re: Grouping the data by month

Astounding — Fri, 15 Feb 2019 20:22:22 GMT

Your ADM_DT variable actually contains datetimes, not dates. It's easier if you convert those to dates:

data want;

set have;

just_the_date = datepart(ADM_DT);

run;

Then you can get the counts directly by applying a format:

proc freq data=want;

tables just_the_date;

format just_the_date mmyyd.;

run;

If you want to use just the dates in a particular year, you can add a subsetting statement before the final RUN statement:

where year(just_the_date) = 2018;

Re: Grouping the data by month

ballardw — Fri, 15 Feb 2019 20:25:53 GMT

@Ranjeeta wrote:

Data I Have

KEY 5482 5253 6942

ADM_DT 29JUN2018:00:00:00 09JUN2018:00:00:00 15JUL2018:00:00:00

DATA WANT

JUN2018 2 Keys

JULY2018 1KEY

Basically I want to count the no of cases that are there in each month in the above e.g 2 keys were between the dates of 01 June 2018 to 30 June 2018

1) those values are DATETIME not dates.

2) is that the way you SAS data set actually looks?

I would expect something that looks more like:

Key ADM_DT

5482 29JUN2018:00:00:00

5253 09JUN2018:00:00:00

6942 15JUL2018:00:00:00

Do you want a report (people read) or a data set(for further processing).

One possible example report:

proc freq data=have ;
   where not missing(key);
   tables adm_dt;
   format adm_dt dtmonyy7.;
run;

the frequency count would be the number of keys.

Re: Grouping the data by month

Ranjeeta — Fri, 15 Feb 2019 20:34:00 GMT

Yes the data looks the way you have described

I want a report that people read

How would I sort by month ?

Re: Grouping the data by month

Reeza — Fri, 15 Feb 2019 20:38:15 GMT

Apply formats to your date variable.

You need to find the format you want, for example DTYEAR may be valid (not sure).

Here's an example of how that can work:

https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb

@Ranjeeta wrote:

Yes the data looks the way you have described

I want a report that people read

How would I sort by month ?

Re: Grouping the data by month

ballardw — Fri, 15 Feb 2019 20:54:15 GMT

@Ranjeeta wrote:

Yes the data looks the way you have described

I want a report that people read

How would I sort by month ?

The proc freq suggested code will sort the output by increasing date by default. That is how proc freq generally works with numeric values such which includes SAS date, datetime and time values. The format will group the counts by month and year.