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.
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 ;
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;
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;
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;
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.