topic Re: How to apply a custom date format? in SAS Programming

How to apply a custom date format?

FarazA_Qureshi — Thu, 19 Sep 2013 06:49:59 GMT

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.

Re: How to apply a custom date format?

LinusH — Thu, 19 Sep 2013 07:13:17 GMT

See my post in your other thread...

Re: How to apply a custom date format?

BrunoMueller — Thu, 19 Sep 2013 07:26:07 GMT

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;

Re: How to apply a custom date format?

FarazA_Qureshi — Thu, 19 Sep 2013 10:19:11 GMT

Sure was an xclent example Bruno!

Highly do oblige your contribution!

Re: How to apply a custom date format?

Tom — Thu, 19 Sep 2013 13:28:53 GMT

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