proc freq to only get month and year from DATE9.

[Justin9]

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;

[Kurt_Bremser]

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

[PaigeMiller]

Change the format to MONYY.

Then run PROC FREQ

[Justin9]

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?

[PaigeMiller · Accepted solution]

---

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

[Justin9]

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;

[Tom]

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;

[Kurt_Bremser]

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;