BookmarkSubscribeRSS Feed
PhatRam33
Fluorite | Level 6

I am trying to format a date field to display in a YY_MM format in proc sql.

 

Example:

09/09/2015  display as 15_09

 

I attempted to use the datepart function: datepart(date_field, 'YY_MM') as date_test however, received this error:

No authorized routine named "DATEPART" of type "DATEPART" having

compatible arguments was found. SQLSTATE=42884

 

Thank you in advance.

 

 

 

6 REPLIES 6
FatCaptain
Fluorite | Level 6

You can create another variable based on your date and format this accordingly.

 

This isn't pretty and will probably be improved on by others but it does work!

 

 

data have ;
format dte ddmmyy10. ;
dte = '03feb2015'd ; output ;
dte = '15apr2002'd ; output ;
dte = '28oct1999'd ; output ;
run ;

proc sql ;
create table want as
select dte,
       substr(compress(put(year(dte),8.)),3,2)||'_'||compress(put(month(dte),z2.)) as dte2
from   have ;
quit ;

Steelers_In_DC
Barite | Level 11

Here you go:

 

data have;
infile cards dsd;
input date;
informat date mmddyy10.;
format date mmddyy10.;
cards;
09/09/2015
;

data want;
set have;
date2 = date;
format date2 yymmn4.;
run;

PGStats
Opal | Level 21

There would many ways to do this, among which (assuming date_field is a datetime value) :

 

data test;
date_field = '15SEP2005:12:00:00'dt;
format date_field datetime19.;
run;

proc sql;
select translate(put(datepart(date_field), yymm5.), "_", "M") as date_string
from test;
quit; 

 

PG
Steelers_In_DC
Barite | Level 11

If it has to be an underscore you have a solution above, but it will not be a date field.  If a slash helps this will work and it maintains being a date field for calculation:

 

data have;
infile cards;
informat date mmddyy10.;
format date mmddyy10.;
input date;
cards;
09/09/2015
;
run;

proc sql;
create table want as
select date,date as date2 format=yymms5.
from have;

PhatRam33
Fluorite | Level 6

Thank you very much everyone for the help.  I will give this a shot and follow-up.  Basically from what I gather, it seems like a 2 step process to format the date field as YY_MM.  Yes, it will no longer be a date type field which is fine so the underscore should be ok.

Tom
Super User Tom
Super User

You could make your own picture format to do that.

 

proc format ;
  picture yy_mm
     low-high = '%0y_%0m' (datatype=datetime)
  ;
run;
data _null_;
  dt=datetime();
  put dt datetime20. / dt yy_mm5. ;
run;

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
  • 6 replies
  • 4886 views
  • 0 likes
  • 5 in conversation