BookmarkSubscribeRSS Feed
AKP
Calcite | Level 5 AKP
Calcite | Level 5

Hi,

I am importing a .txt file which has a column name date of birth in the format 06JUN2012:10:32:13. In the file system its in Char format and now i want to change the vale to numeric date9. .i.e. 06JAUN2012. In the expression window ( File reader), i used put(datepart(DOB),DATE9.) but some how its not working.

Is am missing anything here? Please help!

Thanks

3 REPLIES 3
Patrick
Opal | Level 21

In your external file define for DoB INFORMAT as ANYDTDE. and FORMAT as DATE9.

Used together with the File Reader this will result in an input statement using informat ANYDTDTE. to convert the string in source representing a datetime into a SAS date value, and then using format DATE9. to print this SAS date value.

This way you don't need any expression in the File Reader.

In general if something is not working when setting up stuff in DI Studio: Look at the code generated and the log produced. This often explains where things are going wrong and gives you the idea of how to fix it.

In your case: DATEPART() converts a SAS datetime value into a SAS date value, PUT then converts this value to a string as defined in the format. So you end up with a character value. What possibly would work: You only use DATEPART() in the expression and you then use DATE9. as format assigned to the variable in the output table (so the green grid after the transformation).

RichardinOz
Quartz | Level 8

You say your requirement is for a numeric date variable but you are using the PUT() function, which always returns a character value.  Use the INPUT() function.  You do not need the datepart function if your starting value is already a character value as defined.  The Date9 informat used in the INPUT() function will only read the first 9 characters.

You also say you want the date to appear in Date9 format:

Format Birth_date date9. ;

Birth_date = input(DOB, date9.) ;

Richard in Oz

AKP
Calcite | Level 5 AKP
Calcite | Level 5

Thanks it worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 879 views
  • 0 likes
  • 3 in conversation