topic Re: Related to Reading txt file in SAS does not show all columns in SAS Programming

Re: Related to Reading txt file in SAS does not show all columns

art297 — Tue, 28 Jan 2014 23:01:06 GMT

: I answered this in your previous thread. It would be easiest to do that up front in the data step where you are reading the data:

data work.rev;

INFILE "/vendorlst.txt"

truncOVER

LRECL=32767

FIRSTOBS=2;

length id $10

cust $200;

INPUT id

@'- ' cust &

Year_2011

Year_2012

Year_2013;

RUN;

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Tue, 28 Jan 2014 23:33:59 GMT

By following your above code, still I'm not getting the right result. Here's my code

data work.rev;

INFILE "/vendorlst.txt"

DELIMITER='09'x

MISSOVER

LRECL=32767

FIRSTOBS=2;

LENGTH vndr_id_cust $200

vndr_id $10

cust $ 200;

INPUT Vndr_id_cust

vndr_id $

@'-' cust $

Year_2011

Year_2012

Year_2013;

RUN

Re: Related to Reading txt file in SAS does not show all columns

art297 — Tue, 28 Jan 2014 23:39:59 GMT

that wasn't the code I proposed. Is there a reason why you want to retain the field vndr_id_cust ?

My suggested code simply reads the two fields vndr_id and cust, along with all the rest of your fields.

If you do want to keep that field, too, then just use:

data work.rev;

INFILE "/vendorlst.txt"

truncOVER

LRECL=32767

FIRSTOBS=2;

length vndr_id $10

vndr_id_cust cust $200;

INPUT vndr_id

@'- ' cust &

@1 vndr_id_cust &

Year_2011

Year_2012

Year_2013;

RUN;

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 00:10:10 GMT

I'm still having result issues. when I used the last code above. Here is the output result

so customer column and Year columns are blank because all their information has been put in place under vndr_id_cust column. My result should be something similar as

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 00:22:24 GMT

Thanks, Arthur - Now, when I removed & in @1 vndr_id_cust & the rslt changed to the following which is very close to what I want

Now, how could I populate the cust column?

Re: Related to Reading txt file in SAS does not show all columns

art297 — Wed, 29 Jan 2014 00:48:00 GMT

I suggest running the precise code that I sent or at least post the code that you actually did run.

When I ran the code all of the fields were correctly populated.

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 00:53:45 GMT

data work.rev;

INFILE "/vendorlst.txt"

DELIMITER='09'x

MISSOVER

LRECL=32767

FIRSTOBS=2;

LENGTH

vndr_id $10

vndr_id_cust

cust $ 200;

INPUT

vndr_id

@'-' cust

@1 vndr_id_cust

Year_2011

Year_2012

Year_2013;

format Year_2011 Year_2012 Year_2013 comma18.2;

RUN;

Re: Related to Reading txt file in SAS does not show all columns

art297 — Wed, 29 Jan 2014 01:16:27 GMT

Look at the differences between what you ran and what I suggested. There are a number of differences. You may consider them to be insignificant, but they aren't.

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 02:10:49 GMT

Thanks a lot for your valuable time on this. Please see my comments below and let me know if anything is needed to change my current code.

data work.rev;
INFILE "/vendorlst.txt"
           DELIMITER='09'x-----Yes this is a difference but If I remove as you did on your code, most of the columns will be blank
           MISSOVER---If I change to TRUNCOVER it is same/nothing changed.
           LRECL=32767---same
           FIRSTOBS=2;---same

LENGTH

      vndr_id $10---same
      vndr_id_cust---same
      cust $ 200; ---same

INPUT

      vndr_id
      @'-' cust ---Yes Difference - This is what I don't understand & on your code, Can you please explain this & sign?
      @1 vndr_id_cust---Yes Difference - This is what I don't understand & on your code
      Year_2011
      Year_2012
      Year_2013;

format Year_2011 Year_2012 Year_2013 comma18.2;

Run;

Re: Related to Reading txt file in SAS does not show all columns

art297 — Wed, 29 Jan 2014 02:36:28 GMT

A lot of questions.

As for dlm='09'x

look at your data file outside of SAS, e.g., with notepad (there are ways to look at the records from within SAS, but I always find using notepad the quickest. When you move your cursor between fields, are you moving one character at a time, or immediately jumping to the next field? If it is the latter then, yes, you should use the dlm option. However, if it is the former, you will be providing incorrect instructions by including the dlm option.

Missover. Are all of your data, for each record, on one line. If they are, and the last field happens to be missing, you NEED to use truncover.

Have you ever tried my code exactly as proposed?

@'-' cust ---Yes Difference - This is what I don't understand & on your code, Can you please explain this & sign? If you don't include the &, the input will stop at the first space. Not what you want. The & tells the input statement to continue until it finds at least 2 spaces.

@1 vndr_id_cust---Yes Difference - This is what I don't understand& on your code. Same as explanation as the & with customer as it is reading the same data over again.

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 15:15:20 GMT

Exactly yes I used your code. Below is the code. I used the dlm because as you said when I move my curser between fields it's immediatelly jumping to the next field. Note: I'm using SAS EG 5.1.

Yes all records are one line. here's an example of the raw txt file:

Vndr_id_cust Year_2011 Year_2012 Year_2013

0000002111 - ABC CORPORATION 85032386.25 123252387.54 110031088.50

data work.rev;

INFILE "/vendorlst.txt"

DELIMITER='09'x

TRUNCOVER

LRECL=32767

FIRSTOBS=2;

LENGTH

vndr_id $10

vndr_id_cust cust $200;

INPUT

vndr_id

@'- ' cust &

@1 vndr_id_cust &

Year_2011

Year_2012

Year_2013;

RUN;

Here is the result of this code. Please let me know how vndr_id_cust column info can be distributed into the rest of the fields? so, cust Year_2011 Year_2012 Year_2013 are blank

vndr_id	vndr_id_cust	cust	Year_2011	Year_2012	Year_2013
0000002111	0000002111 - ABC CORPORATION 85032386.25 123252387.54 110031088.50

Re: Related to Reading txt file in SAS does not show all columns

art297 — Wed, 29 Jan 2014 15:40:52 GMT

If your data really are tab delimited then try the following:

data work.rev;

INFILE "/vendorlst.txt"

DELIMITER='09'x

TRUNCOVER

LRECL=32767

FIRSTOBS=2;

LENGTH

vndr_id $10

vndr_id_cust cust $200;

INPUT

vndr_id

@1 vndr_id_cust

Year_2011

Year_2012

Year_2013;

cust=left(substr(vndr_id_cust,index(vndr_id_cust,'-')+1));

RUN;

Re: Related to Reading txt file in SAS does not show all columns

AliMN — Wed, 29 Jan 2014 15:48:38 GMT

Thanks a lot, Arthur. Successfully worked.