How to change a character variable to a numeric date variable

[BrockJarvie]

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;

[jimbarbour]

Try reading it in as ANYDTDTM17. (check the length) and then using DATEPART to get the date portion of the Date-Time value.

Jim

[ballardw]

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.

[jimbarbour]

@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;

[BrockJarvie]

So when you edit the code, are you using Sas studio? Like most answers I get start with “data _;” and end with “run;” that wouldn’t work/ be necessary in the querybuilder/Computer Column of SAS EG . Sorry I’m very new to all of this.

[jimbarbour]

@BrockJarvie,

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

[jimbarbour · Accepted solution]

@BrockJarvie,

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;

[BrockJarvie]

Thank You! That worked like a charm.

Again I appreciate the help!