DATA Step, Macro, Functions and more

Reading Multiple observation per line into one column

Reply
Occasional Contributor
Posts: 15

Reading Multiple observation per line into one column

Hi All,

I am trying to read a comma separatef flat file with following layout.

000000001687561, 000000001915561, 000000002877561, 000000004974561, 000000005703561, 000000006139561, 000000006594561, 000000006612561, 000000006806561, 000000007

108561, 000000007114561, 000000007581561, 000000007682561, 000000008259561, 000000008594561, 000000008864561, 000000009334561, 000000009840561, 000000010167561, 00

01333566192, 041501334051192, 041501340710192, 041501367218192, 041501462985192

I need to read this flat file in on column. Let us say.

obs    id

1     000000001687561

2     000000001915561

3     000000002877561

4    ...

5   .....

I am having issue reading the last record of each line as it goes to next line to complete the record. I am using the following code. Is there better code for this? Can someone please help me out ?

data

test (drop=rec_no i);

Thanks !

infile "c:\aa.txt" dlm = ',' dsd missover

;

length id $ 15

;

retain rec_no 0

;

rec_no +

1

;

do i = 1 to 40

input

i $ @;

if i = ' ' then delete

;

output

;

end

;

run

;

Super User
Posts: 5,081

Reading Multiple observation per line into one column

rookie,

It's simpler than you might think.  Try this:

data test;

infile "c:\aa.txt" dlm=',' dsd;

length id $ 15;

input id @@;

run;

Do NOT add MISSOVER when using @@.

Good luck.

Super User
Posts: 5,081

Reading Multiple observation per line into one column

Oops ... it's a little more complex because of the split records.

data test;

infile "c:\aa.txt" dlm=',' dsd;

length id $ 15;

input id @@;

last_id = lag(id);

if length(id)=15 then do;

   output;

   return;

end;

if length(id) + length(last_id) = 15 then do;

   id = compress(last_id || id);

   output;

end;

keep id;

run;

Sorry about that!

Occasional Contributor
Posts: 15

Reading Multiple observation per line into one column

Thank you very much I tired this code. It is reading only the first line of the raw data file. I will work on this code to make it work.

Super User
Posts: 5,081

Reading Multiple observation per line into one column

Did you remember to remove MISSOVER?

Occasional Contributor
Posts: 15

Reading Multiple observation per line into one column

Yes I did remove the MISSOVER from the code. Logically your code should work. But for some reason this is reading only the first line of the raw file.

Super User
Posts: 5,081

Reading Multiple observation per line into one column

OK, last guess.  It's a little difficult to do this from afar.

Is it possible that there really is only one line of data?  And that the tool you used to examine the raw data file split it into multiple lines for viewing purposes only?

That's easy enough to test.  See how many lines get read in:

data _null_;

infile 'c:\aa.txt';

input id $ 1;

run;

Occasional Contributor
Posts: 15

Reading Multiple observation per line into one column

Indeed there was only one data line in the raw file. The view mode in the text editor was splitting the single dataline into many lines. Thank you for guiding me in the right direction. I really appreciate it !

Super User
Super User
Posts: 6,499

Reading Multiple observation per line into one column

You might need to add LRECL option to the INFILE statement to prevent SAS from truncating the line.  You should be able to tell from the messages in the SAS log.

Super User
Posts: 9,676

Re: Reading Multiple observation per line into one column

OR

recfm=n

data x;

infile 'c:\aa.txt' recfm=n dlm=',';

input x : $50. @@;

cards;

run;

Ksharp

Ask a Question
Discussion stats
  • 9 replies
  • 336 views
  • 0 likes
  • 4 in conversation