Format Date Field in PROC SQL Question

[PhatRam33]

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.

[FatCaptain]

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]

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]

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; 

[Steelers_In_DC]

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]

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]

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;