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.
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;
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
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;
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
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?
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.
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;
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.
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;
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.
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 |
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;
Thanks a lot, Arthur. Successfully worked.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.