topic Re: How do I get month/year frequency of data with timestamp format DATETIME20. ? in SAS Programming

How do I get month/year frequency of data with timestamp format DATETIME20. ?

SAS_hack — Tue, 14 May 2024 15:49:14 GMT

Hello SAS community!

I have a dataset where each entry has a date and timestamp in "DATETIME20." format.

Examples of data entry dates (there are tens of thousands in each month, going back many years):

16FEB2021:07:02:19
28MAR2023:11:52:22
06JAN2024:17:23.51

21APR2024:23:02:38

I am trying to run a proc freq where I get the count of the events by month and year. Ideally the output would look something like this:

NOV23 37248

DEC23 41836

JAN24 39234

FEB24 52868

or even better

NOV2023 37248

DEC2023 41836

JAN2024 39234

FEB2024 52868

My code is presently:

PROC FREQ DATA=FLAGSDAT;
TABLE ENTRY_DATE*FLAG_VALUE / NOPERCENT NOCUM NOROW NOCOL;
RUN;

The current code's output looks like this (using a dummy dataset for speed of testing where all the timestamps are at midnight):

31DEC2023:00:00:00 ‚ 9
01JAN2024:00:00:00 ‚ 11
22JAN2024:00:00:00 ‚ 13
13FEB024:00:00:00 ‚ 12

I tried using this code:

PROC FREQ DATA=FLAGSDAT;
TABLE ENTRY_DATE*FLAG_VALUE / NOPERCENT NOCUM NOROW NOCOL;

FORMAT ENTRY_DATE MONYY.;

RUN;

But that did not work. Any advice for a simple fix to get the dates in the PROC FREQ output to just be MONYY or MONYYYY?

For my dummy data, I would like the output to look like:

DEC2023 9

JAN2024 24

FEB2024 12

Thank you in advance!

Re: How do I get month/year frequency of data with timestamp format DATETIME20. ?

ballardw — Tue, 14 May 2024 20:08:55 GMT

Use the DTMONYY7. format for the Proc Freq.

There are multiple formats to show just the date information similar to a date format for datetime values that have DT as the start of the name.

@SAS_hack wrote:

Hello SAS community!

I have a dataset where each entry has a date and timestamp in "DATETIME20." format.

Examples of data entry dates (there are tens of thousands in each month, going back many years):

16FEB2021:07:02:19
28MAR2023:11:52:22
06JAN2024:17:23.51

21APR2024:23:02:38

I am trying to run a proc freq where I get the count of the events by month and year. Ideally the output would look something like this:

NOV23 37248

DEC23 41836

JAN24 39234

FEB24 52868

or even better

NOV2023 37248

DEC2023 41836

JAN2024 39234

FEB2024 52868

My code is presently:

PROC FREQ DATA=FLAGSDAT;
TABLE ENTRY_DATE*FLAG_VALUE / NOPERCENT NOCUM NOROW NOCOL;
RUN;

The current code's output looks like this (using a dummy dataset for speed of testing where all the timestamps are at midnight):

31DEC2023:00:00:00 ‚ 9
01JAN2024:00:00:00 ‚ 11
22JAN2024:00:00:00 ‚ 13
13FEB024:00:00:00 ‚ 12

I tried using this code:

PROC FREQ DATA=FLAGSDAT;
TABLE ENTRY_DATE*FLAG_VALUE / NOPERCENT NOCUM NOROW NOCOL;

FORMAT ENTRY_DATE MONYY.;

RUN;

But that did not work. Any advice for a simple fix to get the dates in the PROC FREQ output to just be MONYY or MONYYYY?

For my dummy data, I would like the output to look like:

DEC2023 9

JAN2024 24

FEB2024 12

Thank you in advance!

Re: How do I get month/year frequency of data with timestamp format DATETIME20. ?

Patrick — Wed, 15 May 2024 02:15:32 GMT

Like this?

data have;
  input entry_date :datetime20.;
  format entry_date  datetime20.;
  datalines;
16FEB2021:07:02:19
28MAR2023:11:52:22
29MAR2023:11:52:22
06JAN2024:17:23:51
21APR2024:23:02:38
;

proc freq data=have;
  table entry_date ;
  format entry_date dtmonyy7.;
run;