How to apply a custom date format?

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

How to apply a custom date format?

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.


Accepted Solutions
Solution
‎09-19-2013 03:26 AM
SAS Super FREQ
Posts: 683

Re: How to apply a custom date format?

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;

View solution in original post


All Replies
Super User
Posts: 5,256

Re: How to apply a custom date format?

See my post in your other thread...

Data never sleeps
Solution
‎09-19-2013 03:26 AM
SAS Super FREQ
Posts: 683

Re: How to apply a custom date format?

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;
Contributor
Posts: 51

Re: How to apply a custom date format?

Sure was an xclent example Bruno!

Highly do oblige your contribution!

Super User
Super User
Posts: 6,500

Re: How to apply a custom date format?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1032 views
  • 1 like
  • 4 in conversation