topic Re: Importing difficult txt file in SAS Programming

Importing difficult txt file

kbanders — Fri, 28 May 2021 19:33:57 GMT

hello!

I am trying to import a txt file, but I am having difficulty because I do not understand the delimiter in the file.I originally thought it was space delimited (code below), but the variables and columns do not match up. I have attached a sample of the data and data dictionary below. The end result should be 50 variables, not all will have values. Any assistance in making the variables and columns matched up would be much appreciated!

proc import OUT=mydata

datafile= "\\computer\data\data sample.txt"

DBMS=dlm REPLACE;

run;

Re: Importing difficult txt file

Reeza — Fri, 28 May 2021 19:42:31 GMT

You have a fixed width file/column input, not a delimited file.

https://stackoverflow.com/questions/27596072/reading-fixed-width-file-spaces-are-recognized
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n13ejk9swz5vrbn0z34iazfrp0wp.htm

Re: Importing difficult txt file

mkeintz — Fri, 28 May 2021 19:55:52 GMT

And because, as @Reeza noted, you have a fixed width text file, you have a very easy program to write in a data step.

Re: Importing difficult txt file

ballardw — Fri, 28 May 2021 19:58:44 GMT

@mkeintz wrote:

And because, as @Reeza noted, you have a fixed width text file, you have a very easy program to write in a data step.

If the dictionary has the correct columns.

Re: Importing difficult txt file

Sajid01 — Sat, 29 May 2021 02:21:39 GMT

Hello @kbanders
You have simple reading to be done.

Unfortunately I see error in your dictionary file.
I have created a proof of concept code you can use it to create your code which you may use at basis. Make changes as you may need. I am attaching my data file too.

data test;
Length Mem_id $10 Fam_mem_id $2 Claim_numbr $15;
infile "/Path_to_your_file/sample.txt"  truncover;
input @1 Mem_id $ @12 Fam_mem_id $ @14 Claim_numbr $;
run;

My sample data is attached.

Re: Importing difficult txt file

Patrick — Sat, 29 May 2021 04:17:14 GMT

@kbanders

Should you still have problems importing your data after reading through the docu links shared on how to impute fixed length records then please post a data dictionary and data that matches.

Also post the data step code you've already developed and can't make fully working. If there is a header section in your data then use option FIRSTOBS=<some number> on the Infile statement to only start imputing the data portion of your .txt file.

If you look at the Excel you've shared then:

- Tab Header Record indicates that there is a header record in your data. The txt file you've shared doesn't have such a header

- Tab Summary shows #REF instead of the start & end positions of the data (I could fix this issue)

And "worst" of all: In your .txt file the data in your first "column" is a string of 10 digits. This doesn't match your data dictionary where the first variable MBRID goes from position 1 to 30.

If you need further help then please post your data dictionary as .csv because many people won't download and open .xlsx