I'm on a problem that is asking me to extract the date from a character variable(RegisteredDate) and change it to a numeric date variable with the DATE7 format.
I need to create a new computed column called RegisteredDate_New. Which functions should I use to do this?
I've been trying the DATE(), and INPUT() functions. Please let me know which functions would work for this problem.
*This is the code from the Query builder of the Data file I'm working on. @jimbarbour @ballardw Where would I insert the Code? Again I'm a noob, so I really appreciate the help. Thank you.
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FORECLOSURES AS
SELECT t1.APN,
t1.RegisteredDate,
t1.PropertyType,
t1.PropertyAddress,
t1.CouncilDistrict,
t1.Lender,
t1.LenderContact,
t1.LenderPhone,
t1.PropertyManagement,
t1.PropertyManagementContact,
t1.PropertyManagementAddress,
t1.PropertyMgmtContactPhone
FROM WORK.FORECLOSURES t1;
QUIT;
Try something like the below. I'm doing this from memory. I think the FORMAT comes after the AS, but try it the other way around if it doesn't work.
Jim
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FORECLOSURES AS
SELECT t1.APN,
DATEPART(INPUT(t1.RegisteredDate, ANYDTDTM19.)) AS RegisteredDateNew FORMAT=DATE7.,
t1.PropertyType,
t1.PropertyAddress,
t1.CouncilDistrict,
t1.Lender,
t1.LenderContact,
t1.LenderPhone,
t1.PropertyManagement,
t1.PropertyManagementContact,
t1.PropertyManagementAddress,
t1.PropertyMgmtContactPhone
FROM WORK.FORECLOSURES t1;
QUIT;
Try reading it in as ANYDTDTM17. (check the length) and then using DATEPART to get the date portion of the Date-Time value.
Jim
data example;
z = '2017-10-26T00:00:00';
x = input(z,yymmdd10.);
format x date7.;
run;
specifying a length on the input will only read that number of characters.
In the future it may help to post example data as text, not pictures. It is very hard to write code against pictures. It would also be a good idea to post actual code attempted.
@ballardw's code should do you.
If you're reading in text, here's a ready-made example using DATEPART(). Results are below the code.
Jim
Data Date_Data;
FORMAT RegisteredDate DATETIME17.;
FORMAT RegisteredDateNew DATE9.;
INPUT RegisteredDate : ANYDTDTM17.;
RegisteredDateNew = DATEPART(RegisteredDate);
DATALINES;
2017-10-26T00:00:00
;
Run;
I'm using Enterprise Guide 8.2.
If you hold down the Ctrl key on your keyboard and press "N" while continuing to hold down the Ctrl key, a Program window should open up. You can just cut and paste in the code I posted above and try running it by either clicking the little SAS running man icon or by pressing F3. Play with the code a bit. See what it does.
There's a lot more to EG than Query Builder.
Jim
Try something like the below. I'm doing this from memory. I think the FORMAT comes after the AS, but try it the other way around if it doesn't work.
Jim
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FORECLOSURES AS
SELECT t1.APN,
DATEPART(INPUT(t1.RegisteredDate, ANYDTDTM19.)) AS RegisteredDateNew FORMAT=DATE7.,
t1.PropertyType,
t1.PropertyAddress,
t1.CouncilDistrict,
t1.Lender,
t1.LenderContact,
t1.LenderPhone,
t1.PropertyManagement,
t1.PropertyManagementContact,
t1.PropertyManagementAddress,
t1.PropertyMgmtContactPhone
FROM WORK.FORECLOSURES t1;
QUIT;
Thank You! That worked like a charm.
Again I appreciate the help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.