BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AliMN
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

13 REPLIES 13
art297
Opal | Level 21

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

AliMN
Calcite | Level 5

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

art297
Opal | Level 21

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;

AliMN
Calcite | Level 5

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

AliMN
Calcite | Level 5

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?

art297
Opal | Level 21

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.

AliMN
Calcite | Level 5

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;

art297
Opal | Level 21

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.

AliMN
Calcite | Level 5

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;

art297
Opal | Level 21

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.



AliMN
Calcite | Level 5

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
art297
Opal | Level 21

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;

AliMN
Calcite | Level 5

Thanks a lot, Arthur. Successfully worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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