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

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

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

One of my columns in the text file has a column that contain vndr_id with customer name that I would like to break it as follows.

Now, the file has Vndr_id_cust column and I would like to split vndr_id and cust into two columns (e.g: vndr_id and Cust). Any help is appreciated.

Capture.JPG


Accepted Solutions
Solution
‎01-29-2014 10:40 AM
PROC Star
Posts: 7,467

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

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;

View solution in original post


All Replies
PROC Star
Posts: 7,467

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

:  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;

Contributor
Posts: 49

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

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

PROC Star
Posts: 7,467

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

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;

Contributor
Posts: 49

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

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

Capture.JPG

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

Capture1.JPG

Contributor
Posts: 49

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

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  Capture3.JPG

Now, how could I populate the cust column?

PROC Star
Posts: 7,467

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

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.

Contributor
Posts: 49

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

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;

PROC Star
Posts: 7,467

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

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.

Contributor
Posts: 49

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

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;

PROC Star
Posts: 7,467

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

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.



Contributor
Posts: 49

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

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_idvndr_id_custcustYear_2011Year_2012Year_2013
00000021110000002111 - ABC CORPORATION 85032386.25 123252387.54 110031088.50
Solution
‎01-29-2014 10:40 AM
PROC Star
Posts: 7,467

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

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;

Contributor
Posts: 49

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

Thanks a lot, Arthur. Successfully worked.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 369 views
  • 0 likes
  • 2 in conversation