topic Format Date Field in PROC SQL Question in SAS Programming

Format Date Field in PROC SQL Question

PhatRam33 — Wed, 09 Sep 2015 15:42:27 GMT

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.

Re: Format Date Field in PROC SQL Question

FatCaptain — Wed, 09 Sep 2015 15:56:20 GMT

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 ;

Re: Format Date Field in PROC SQL Question

Steelers_In_DC — Wed, 09 Sep 2015 16:19:15 GMT

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;

Re: Format Date Field in PROC SQL Question

PGStats — Wed, 09 Sep 2015 17:02:49 GMT

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;

Re: Format Date Field in PROC SQL Question

Steelers_In_DC — Wed, 09 Sep 2015 17:22:09 GMT

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;

Re: Format Date Field in PROC SQL Question

PhatRam33 — Wed, 09 Sep 2015 18:39:36 GMT

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.

Re: Format Date Field in PROC SQL Question

Tom — Sat, 12 Sep 2015 13:35:04 GMT

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;