Convert Data string to Date String MONYY7.

[Giuliano]

I am running the code below.  I added the sysdate to my first 2 tables.  The sysdate keeps coming out as a character string.  I would like to change it to a date string and only display the month and year (MONYY7.)  Can anyone please assist me with modifiying my current code or adding a data step to convert the character string to a date string and display month and year only. ex the date right now is diplayed as 09JAN15 which is formated as CHAR7.  I would like to display the date as JAN2015.

Thank you,

PROC SQL;
CREATE TABLE work.mobpp1 AS
SELECT DISTINCT soc, feature_code, rate,
    datepart(effective_date)format=mmddyy10. AS effective_date,
    datepart(sys_creation_date)format=mmddyy10. AS sys_creation_date,
    datepart(expiration_date)format=mmddyy10. AS expiration_date,
    "&sysdate" as Extract_Date
FROM bmlrwork.pp_rc_rate
WHERE expiration_date =.;
QUIT;

DATA work.mobpp1;
INPUT Extract_Date $7.;
FMTExtract_Date= INPUT(Extract_Date,monyy7.);
RUN;

PROC SQL;
CREATE TABLE work.mobpp2 AS
SELECT DISTINCT soc, soc_description, product_type, "&sysdate" AS Extract_Date
FROM bmlrwork.soc
WHERE expiration_date =.;
QUIT;

PROC SQL;
CREATE TABLE work.mobpp3 AS
SELECT DISTINCT t1.soc, t1.feature_code, t1.rate, t1.effective_date, t1.sys_creation_date, t1.expiration_date,
    t2.soc_description, t2.product_type, t2.Extract_Date
FROM work.mobpp1 AS t1
LEFT JOIN work.mobpp2 AS t2
ON t1.soc = t2.soc;
QUIT;

Message was edited by: Giuliano Filoso

[Haikuo]

Replace

"&sysdate" AS Extract_Date

with

input("&sysdate.", date9.) AS Extract_Date  format=monyy7.

BTW, if what you want is Today's date, then using date() or today() maybe more straightforward.

date()   AS Extract_Date  format=monyy7.

[Loko · Accepted solution]

hello,

just add d after your sysdate variable and you will have the corresponding numeric sas vaue which can be formatted at your will.

PROC SQL;

CREATE TABLE work.mobpp1 AS

SELECT DISTINCT soc, feature_code, rate,

    datepart(effective_date)format=mmddyy10. AS effective_date,

    datepart(sys_creation_date)format=mmddyy10. AS sys_creation_date,

    datepart(expiration_date)format=mmddyy10. AS expiration_date,

    "&sysdate"d as Extract_Date

FROM bmlrwork.pp_rc_rate

WHERE expiration_date =.;

QUIT;