Learning SAS? Welcome to the exclusive online community for all SAS learners.

How to import messy txt file with header and data in multiple rows??

Reply
New Contributor
Posts: 3

How to import messy txt file with header and data in multiple rows??

I have to import a txt file with "|" delimiter where header and data in into 2 rows as stated below

H1 "|" H2

H3 "|" H4

D1 "|" D2

D3 "|" D4

D1 "|" D2

D3 "|" D4

Please help in this regards as there are multiple files with over 150 column names

Respected Advisor
Posts: 3,156

Re: How to import messy txt file with header and data in multiple rows??

Are you looking for some SAS code? If so, the following may get you started. Pay extreme attention to those funky quotes.

filename FT15F001 temp;

data _NULL_;

     infile FT15F001 DLM='"|"' OBS=2;

     INPUT (VAR1-VAR2) (:$10.);

     IF _N_=1 THEN

           CALL EXECUTE('DATA WANT; INFILE FT15F001 DLM='||"'"||'"'||'|'||'"'||"'"|| ' FIRSTOBS=3;

           INPUT '||VAR1||':$10. '||VAR2||' :$10.;');

     IF _N_=2 THEN

           CALL EXECUTE('INPUT '||VAR1||':$10. '||VAR2||' :$10.;RUN;');

     parmcards;

H1 "|" H2

H3 "|" H4

D1 "|" D2

D3 "|" D4

D1 "|" D2

D3 "|" D4

;;;;

run;

Super User
Super User
Posts: 7,039

Re: How to import messy txt file with header and data in multiple rows??

Doesn't look hard to me.  Just pull the first lines into your program editor and use the values as the names of your variables.

For example here is program that reads your sample data.

2270  data xx;

2271    infile cards dlmstr='"|"' truncover firstobs=3;

2272    length h1-h4 $10 ;

2273    input h1--h2 / h3--h4 ;

2274    put (_all_) (=);

2275  cards;

h1=D1 h2=D2 h3=D3 h4=D4

h1=D1 h2=D2 h3=D3 h4=D4

Regular Contributor
Posts: 168

Re: How to import messy txt file with header and data in multiple rows??

When I ran the below code, I don't get any values for any of the variables. Am I doing anything wrong?

data xx;

  infile cards dlmstr='"|"' truncover firstobs=3;

  length h1-h4 $10 ;

  input h1--h2 / h3--h4 ;

  put (_all_) (=);

cards;

H1 "|" H2

H3 "|" H4

;

run;

SAS Employee
Posts: 340

Re: How to import messy txt file with header and data in multiple rows??

@Tom 's code is correct. But also put the data part (not just header) into the "card area":

data xx;

  infile cards dlmstr='"|"' truncover firstobs=3;

  length h1-h4 $10 ;

  input h1--h2 / h3--h4 ;

  put (_all_) (=);

cards;

H1 "|" H2

H3 "|" H4

D1 "|" D2

D3 "|" D4

D1 "|" D2

D3 "|" D4

;

run;

Of course, when you run this data step against your real file, you will remove the whole "cards area", and also will change the infile statement to point to your real file.

Super User
Super User
Posts: 7,039

Re: How to import messy txt file with header and data in multiple rows??

Your INFILE statement says to start reading on line 3 and you only have two lines of data.

Regular Contributor
Posts: 168

Re: How to import messy txt file with header and data in multiple rows??

what does slash (/) mean in input statement?

input h1--h2 / h3--h4 ;

Super User
Super User
Posts: 7,039

Re: How to import messy txt file with header and data in multiple rows??

Read about line pointer controls.  / tells it to move to the next line.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146292.htm

Regular Contributor
Posts: 168

Re: How to import messy txt file with header and data in multiple rows??

Thanks for your feedback.

New Contributor
Posts: 3

Re: How to import messy txt file with header and data in multiple rows??

Thanks for the reply guys. May be i couldn't explain my problem.

I have a txt file which looks like this. The column name and data is split in two rows. Please help with the code

Occasional Contributor
Posts: 17

Re: How to import messy txt file with header and data in multiple rows??

Hi Sidd,

hope the following code will help you,

filename file1 'drive_path\file_name.txt';

data want;

  infile file1 dlm='"|"';

  input H1$ H2$@@ H3$ H4$;

run;

Regards,

Yogesh

New Contributor
Posts: 3

Re: How to import messy txt file with header and data in multiple rows??

Thanks a ton guys!! It worked for me!!

Ask a Question
Discussion stats
  • 11 replies
  • 894 views
  • 10 likes
  • 6 in conversation