BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FarazA_Qureshi
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

4 REPLIES 4
LinusH
Tourmaline | Level 20

See my post in your other thread...

Data never sleeps
BrunoMueller
SAS Super FREQ

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
Calcite | Level 5

Sure was an xclent example Bruno!

Highly do oblige your contribution!

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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