SAS - Import text file where date separator and delimiter are same

Question

I have received some text files that have been extracted from SQL Server and need to be imported into SAS. The problem is that the delimiter is a colon ':'

And this file also contains datetime fields where the seconds are separated by the same delimiter.

I cannot change the text file extract procedure for these now since these files are backdated extracts.

Sample Data:

ABCDEFGHI:2015-06-03 00:00:00.000:XYZ
DEFGHI::XYZ
GHIJ:2015-06-04 04:43:19.660:KLMN

Since the variables have varying lengths, I can't import this as a fixed length file. I tried importing the datetime fields into separate date, hour, minute and seconds fields but the dates are missing on some records which means these records won't have sufficient number of delimiters.

Please help. Is there any way I can import this file successfully into SAS?

Tom Tom · Accepted Answer · 2017-04-19T03:31:25

First, go back to the source and request that they export the file in a usable format. If they cannot fix it ask if they can move the problem field to the end of the line.

data want ;
   infile 'myfile.txt' dsd dlm=':' truncover ;
   length var1 $20 var2 $8 datetime 8 ;
   input var1 var2 datetime anydtdtm23. ;
   format datetime datetime24.3 ;
run;

But if you have just the one field that can contain extra delimiters and the number of delimiters that appear when that field is not null is fixed or the length is fixed when not null then you should be able to program around the problem.

So in your example you could just read the last two fields into a character variable and then parse it yourself.

data want ;
   infile cards dsd dlm=':' truncover ;
   length var1 $20 datetime 8 var2 $8 ;
   input var1 str $50.;
   var2 = scan(str,-1,':');
   datetime = input(substrn(str,1,length(str)-length(var2)-1),anydtdtm.);
   format datetime datetime24.3 ;
cards;
ABCDEFGHI:2015-06-03 00:00:00.000:XYZ
DEFGHI::XYZ
GHIJ:2015-06-04 04:43:19.660:KLMN
A::
;

SAS - Import text file where date separator and delimiter are same

1 Answers