import multiple text files with missing values

medic — Mon, 09 Apr 2018 17:47:05 GMT

Hi all,

I'm trying to import multiple text files with comma(,) as delimiter, and make it as one dataset.

I refered to the following post and wrote a program as follows:

https://communities.sas.com/t5/General-SAS-Programming/read-in-multiple-txt-files-in-to-one-SAS-file/td-p/86690

my program:

filename file pipe 'dir D:\test\*.txt /s /b';
data test.t200_test;
	infile file truncover;
	input fname $100.;
	infile in filevar=fname end=last truncover dlm=',' firstobs=1;
	do until(last);
		input A $ B $ C $ D $;
		output;
	end;
run;

the problem I'm struggling is that in the data file, there are some missing values such as

123, A01, 20180401, 54321

234, , 20180401, 23412 ( <- missing data in the second column )

345, D31, 20180402, 12345

in this case, the dataset from the code above looks like

A　　　B　　　　　C　　　　　D

123　　A01　　　　20180401　54321

234　　20180401　 23412

345　　D31　　　　20180402　12345

which means, the program does not recognize that there's a missing value in second row of the column B, and just enters the value which is supposed to be in column C, and eventually leaving the last column empty.

But if there's no missing value on the next row, then the data is entered correctly again.

However, if I don't use this program and manually import the data files one by one using proc import procedure or import wizard, then there's no problem like this and becomes as what it should look like :

A　　　B　　　　　C　　　　　D

123　　A01　　　　20180401　54321

234　　.　　　　 20180401　23412

345　　D31　　　　20180402　12345

Thank you in advance for your help!

Re: import multiple text files with missing values

Reeza — Mon, 09 Apr 2018 17:46:20 GMT

Add the DSD option to your INFILE statement?

Re: import multiple text files with missing values

medic — Mon, 09 Apr 2018 17:50:51 GMT

wow... Thank you sooooo much. It works now!!
I was struggling with this whole day T.T
Thanks a lot!!

topic Re: import multiple text files with missing values in SAS Programming

import multiple text files with missing values

Re: import multiple text files with missing values

Re: import multiple text files with missing values