How to apply a custom date format?

[FarazA_Qureshi]

In an SQL statement I need to have the results returning a custom date format of a field MIS_DATE as YYYY_MM in other words a date like 31JAN2013 being returned as 2013_01. A relevant change in this regard in the following statement shall be obliged:

PROC SQL;

CREATE TABLE RESULTS3 AS SELECT MIS_DATE AS DATA_MONTH format = YYYY_MM. FROM FAQ;

QUIT;

The process runs well without any observation in the Log but when I double click the Results3 dataset the same doesnot open and only then comes up the entry in Log:

ERROR: Format YYYY_MM not found or couldn't be loaded for variable DATA_MONTH.

Thanx in advance.

[LinusH]

See my post in your other thread...

[BrunoMueller · Accepted solution]

SAS allows you to build custom date formats using Proc FORMAT, see an example below. Have a look at the doc for the PICTURE statement in Proc FORMAT for more information on other directives possible in a picture format.

data have;
  do month = 1 to 12;
    myDate = mdy(month, 1, year(today()));
    output;
  end;

  format myDate date9.;
run;

proc format;
  picture x_yyyy_mm (default=7)
    low - high = '%Y_%0m' (datatype=date)
  ;
run;

proc sql;
  select
    myDate
    , myDate as mydate2 format=yymmd.
    , myDate as mydate3 format=x_yyyy_mm.
  from
    have
  ;
quit;

[FarazA_Qureshi]

Sure was an xclent example Bruno!

Highly do oblige your contribution!

[Tom]

If you just want the data as a character string (as opposed to a numeric date value with a particular display format attached to it) then you can build it easily.

select catx('_',year(MIS_DATE),month(MIS_DATE)) as DATA_MONTH

or if you need 2013_01 instead of 2013_1

select catx('_',year(MIS_DATE),put(month(MIS_DATE),Z2.)) as DATA_MONTH