topic Re: proc freq to only get month and year from DATE9. in SAS Programming

proc freq to only get month and year from DATE9.

Justin9 — Mon, 06 Apr 2020 11:59:38 GMT

Hi,

Please can someone help me with code that will show the frequency of of my "APPLICATION" variable (which is a numeric, DATE9. variable), but in terms of only month and year? For example, from my screenshot below (example extract of my dataset that contains 50,000 observations), a proc freq would output that there is one account from FEB2018, three accounts from JUL2019 and one account from JAN2020.

Would it be better to create a new variable (e.g. "APPLICATION_FINAL") that only keeps the month and year from "APPLICATION", then run a proc freq on the new variable (e.g. "APPLICATION_FINAL"). Or can a proc freq be done on "APPLICATION" without overriding the data? Thanks!

proc freq data = cd.test;

table ................. ;

run;

Re: proc freq to only get month and year from DATE9.

Kurt_Bremser — Mon, 06 Apr 2020 11:56:39 GMT

Just use a proper format; proc freq, like other statistic procedures, will use the formatted value automatically:

data have;
input date :yymmdd10.;
format date yymmd7.;
datalines;
2020-03-05
2020-03-08
2020-04-01
2020-05-07
;

proc freq data=have;
tables date;
run;

Die Prozedur FREQ


date	Häufigkeit	Prozent	Kumuliert Häufigkeit	Kumuliert Prozent
2020-03	2	50.00	2	50.00
2020-04	1	25.00	3	75.00
2020-05	1	25.00	4	100.00

Re: proc freq to only get month and year from DATE9.

PaigeMiller — Mon, 06 Apr 2020 11:57:39 GMT

Change the format to MONYY.

Then run PROC FREQ

Re: proc freq to only get month and year from DATE9.

Justin9 — Mon, 06 Apr 2020 12:07:30 GMT

Thanks, so in order to avoid overriding "APPLICATION", would I have to create a new variable e.g. "APPLICATION_FINAL" so that it's APPLICATION_FINAL=put(APPLICATION,monyy7.); , then run a proc freq on APPLICATION_FINAL?

A proc freq should then help me output (based on my extract screenshot) that there is one account from FEB2018, three accounts from JUL2019 and one account from JAN2020?

Re: proc freq to only get month and year from DATE9.

PaigeMiller — Mon, 06 Apr 2020 12:46:30 GMT

@Justin9 wrote:

Thanks, so in order to avoid overriding "APPLICATION", would I have to create a new variable e.g. "APPLICATION_FINAL" so that it's APPLICATION_FINAL=put(APPLICATION,monyy7.); , then run a proc freq on APPLICATION_FINAL?

No, you simply need to apply a different format. There's no need for a new variable or overwriting data sets.

proc freq data=cd.test;
     table application;
     format application monyy.;
run;

Or, you could use PROC DATASETS to change the format without creating a new dataset and without creating a new variable, or you could apply the desired format at the time the data set is created.

Re: proc freq to only get month and year from DATE9.

Justin9 — Mon, 06 Apr 2020 13:16:59 GMT

Thanks a lot, it works! Just as a reference, do you mind telling me how this format would work when creating a proc sql table? Please can you edit my code below, where necessary?

proc sql;
create table test as

select month
, APPLICATION format=monyy.
from cd.test
group by month
, APPLICATION ;
quit;

Re: proc freq to only get month and year from DATE9.

Tom — Mon, 06 Apr 2020 13:22:54 GMT

The SQL language is not SAS, it doesn't understand such concepts.

So you will need to tell it EXACTLY what you want it to do. To get something similar to what PROC FREQ would do you might do something like:

proc sql;
  create table test as
  select min(date) as date format=monyy7.
      , count(*) as count
  from have
  group by put(date,monyy7.)
  ;
quit;

Re: proc freq to only get month and year from DATE9.

Kurt_Bremser — Mon, 06 Apr 2020 13:23:33 GMT

Please use the "little running man" to post code, and work on your code formatting.

In SQL, you need correct values in a column for group by to work properly:

proc sql;
create table test as
  select
    month,
    put(APPLICATION,yymmd7.) as application
  from cd.test
  group by month, calculated application
;
quit;