Grouping the data by month

[Ranjeeta]

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

[novinosrin]

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 🙂

[Ranjeeta]

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

[ballardw]

---

@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.

[Ranjeeta]

Yes the data looks the way you have described

I want a report that people read

How would I sort by month ?

[Reeza]

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 ?

---

[ballardw]

---

@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.

[Astounding]

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;