Input a text file, the output date format is missing

[sarahzhou]

I have an input txt file and uploaded it to my working directory, when I read and format the DATE_JOINED column, the date is missing, code as following:

data example_txt;
	infile '/home/u59667351/example_csv/example_csv.txt' dlm='<|>' firstobs=2;
	input PERSON_ID $ DEPT_ID $ DATE_JOINED yymmdd10. ;
	format DATE_JOINED yymmdd10.;
run;

the text file named example_csv as following:

PERSON_ID<|>DEPT_ID<|>DATE_JOINED
AAAAA<|>S1<|>2021/01/03
BBBBBB<|>S2<|>2021/02/03
CCCCC<|>S1<|>2021/03/05

The OUTPUT date DATE_JOINED is missing.

Any suggestion?

Thanks

[Shmuel]

When I run next code:

data example_txt;
	infile CARDS dlm='<|>' firstobs=2;
	input PERSON_ID $ DEPT_ID $ DATE_JOINED $10.; * yymmdd10. ;
	*format DATE_JOINED yymmdd10.;
cards;
PERSON_ID<|>DEPT_ID<|>DATE_JOINED
AAAAA<|>S1<|>2021/01/03
BBBBBB<|>S2<|>2021/02/03
CCCCC<|>S1<|>2021/03/05
;
run; proc print; run;

I got next result:

Obs	PERSON_ID DEPT_ID  DATE_JOINED
1	AAAAA	  S1	   |>2021/01/
2	BBBBBB	  S2	   |>2021/02/
3	CCCCC	  S1	   |>2021/03/

as you see sas doesn't read the DATE_JOINED correctly.

When I change the input to a real csv (comma delimited) it works fine.

There is probably a non printable character inside the last delimiter.

I'm going to check it.

[andreas_lds]

The docs of dlm/delimiter say:

specifies one or more characters to read as delimiters.

I have highlighted the important char. You need to use option dlmstr instead, because your have only one delimiter in your data, the string "<|>".

[sarahzhou · Accepted solution]

This one works. Thank you!

[sarahzhou]

It works!

[Shmuel]

@andreas_ldsת, you are right that dlmstr solves the issue, but it is very starnge why the first two variables were read correctly and only the last variable was read incorrectly, as I show in my post of result:

When I run next code:

data example_txt;
	infile CARDS dlm='<|>' firstobs=2;
	input PERSON_ID $ DEPT_ID $ DATE_JOINED $10.; * yymmdd10. ;
	*format DATE_JOINED yymmdd10.;
cards;
PERSON_ID<|>DEPT_ID<|>DATE_JOINED
AAAAA<|>S1<|>2021/01/03
BBBBBB<|>S2<|>2021/02/03
CCCCC<|>S1<|>2021/03/05
;
run; proc print; run;

I got next result:

Obs	PERSON_ID DEPT_ID  DATE_JOINED
1	AAAAA	  S1	   |>2021/01/
2	BBBBBB	  S2	   |>2021/02/
3	CCCCC	  S1	   |>2021/03/

 It seems to be a bug ?!

[Tom]

That is just the difference between FORMATTED input and LIST MODE input.

With LIST MODE input the INPUT statement will skip over the delimiters the cursor it currently pointed at. So the extra pipe and greater than character are ignored when reading DEPT_ID.

Use the : modifier in front of any informat specifications you have included in the INPUT statement to allow the INPUT statement to work in LIST MODE instead of FORMATTED mode.

input PERSON_ID $ DEPT_ID $ DATE_JOINED :yymmdd. ;

[AMSAS]

This is not odd or a bug, what you are doing is mixing and matching input methods.

input PERSON_ID $ DEPT_ID $ DATE_JOINED $10. ; * yymmdd10. ;

So PERSON_ID & DEPT_ID are both considered list input

Whereas the DATE_JOINED is considered formatted input as you define a format to use ($10.)

To get the DATE_JOINED to be read correctly you need to change it to use list input, by using the : modifier and supply a format:

input PERSON_ID $ DEPT_ID $ : DATE_JOINED $10. ; * yymmdd10. ;

Now for the why
The DLM option on the INFILE statement uses any of the characters as delimiters, not the whole string. So SAS sees PERSON_ID as "AAAAA" followed by 3 delimiters "<", "|" & ">". The next variable is DEPT_ID "S1" at this point the line pointer is pointing at the "|" and you switch to formatted input and read the next 10 characters "|>2021/01/".
DLMSTR works because SAS sees the whole "<|>" as a single delimiter and the line pointer is now at the start of the date.

[Shmuel]

Thanks @Tom , it is clear now, and sorry for my late response.