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
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;
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.