DATA Step, Macro, Functions and more

Format Date Field in PROC SQL Question

Reply
Contributor
Posts: 23

Format Date Field in PROC SQL Question

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.

 

 

 

Contributor
Posts: 29

Re: Format Date Field in PROC SQL Question

Posted in reply to PhatRam33

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 ;

Valued Guide
Posts: 860

Re: Format Date Field in PROC SQL Question

Posted in reply to PhatRam33

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;

Respected Advisor
Posts: 4,925

Re: Format Date Field in PROC SQL Question

[ Edited ]
Posted in reply to PhatRam33

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
Valued Guide
Posts: 860

Re: Format Date Field in PROC SQL Question

Posted in reply to PhatRam33

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;

Contributor
Posts: 23

Re: Format Date Field in PROC SQL Question

Posted in reply to Steelers_In_DC

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.

Super User
Super User
Posts: 7,050

Re: Format Date Field in PROC SQL Question

Posted in reply to PhatRam33

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;
Ask a Question
Discussion stats
  • 6 replies
  • 369 views
  • 0 likes
  • 5 in conversation