BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Beto16
Obsidian | Level 7
Hi I have a table that has the data in a row...it looks like this
Tracking
"Barcode"." SA0258220607"

I need to extract SA0258220607

the issue is that the data I need is in rows some times there might be 5 entries in one row or 20 entries


"Barcode"." SA0258220607"
"Barcode"." PA0258220707"
"Barcode"." Sk0258220607"
"Barcode"." SSY025822077"
I would like to convert the rows into one col....than extract just what I need was thinking of doing a proc transpose

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input x $80.;
length Bacode $ 20;
n+1;
pid=prxparse('/atmbarcode:"\w+/i');
s=1;
e=length(x);
call prxnext(pid,s,e,x,p,l);
do while(p>0);
 Bacode=substr(x,p,l);
 output;
 call prxnext(pid,s,e,x,p,l);
end;
keep n x Bacode ;
cards;
atmbarcode:"FE235. atmbarcode:"Yu23
;
run;
proc transpose data=have out=want(drop=_name_) prefix=Bacode;
by n x;
var Bacode;
run;

View solution in original post

26 REPLIES 26
Astounding
PROC Star

If you would like to convert each row into multiple rows, that is up to you.  Here's a solution that reads multiple entries from the same row:

 

data want;

infile rawdata;

input @;

length SA_string $ 20;

do i=1 to countw(_infile_, '.');

   SA_string = scan(_infile_, 3*i, '"');

   output;

end;

run;

 

There might be some tweaking needed, since you haven't shown what the data looks like when there are multiple entries per row.  Who knows?  Maybe my imagination is correct.

Beto16
Obsidian | Level 7
Thanks for response. I have a question can I use scan to look only for a specific word like Barcode instead of "? ... The entries can vary per row
Id. Bacode1 Was1 Bacode2 was2 Bacode3 was3
Qw. Bar Az011. ABC. BarAz345. Wer.BarAz056.
Tr. BrAz078. Dfg. . Braz078. Tyr.
Ee. Br Az089. Rty
I would like to see

Id Barcode1 Barcode2. Barcode3
Qw Az011. Az345. Az056.
Tr. Az078. Az078.
Ee. Az089.
Reeza
Super User

Are your records always in pairs, or you're always using the second one? What's your delimiter in the second row, it doesn't look like there's any. 

You need to explain all the rules.

Astounding
PROC Star

Here's the documentation that shows what the SCAN function can do:

 

http://support.sas.com/documentation/cdl/en/syntaxidx/68719/HTML/default/index.htm#/documentation/cd...

 

Ksharp
Super User

Does the Barcode always like AZ98989 ?

 

data have;
input x $80.;
length Bacode $ 20;
n+1;
pid=prxparse('/az\d+/i');
s=1;
e=length(x);
call prxnext(pid,s,e,x,p,l);
do while(p>0);
 Bacode=substr(x,p,l);
 output;
 call prxnext(pid,s,e,x,p,l);
end;
keep n x Bacode ;
cards;
Qw. Bar Az011. ABC. BarAz345. Wer.BarAz056.
Tr. BrAz078. Dfg. . Braz078. Tyr.
Ee. Br Az089. Rty
;
run;
proc transpose data=have out=want(drop=_name_) prefix=Bacode;
by n x;
var Bacode;
run;
Beto16
Obsidian | Level 7
Hi Ksharp
Thank you for response I seem to have gotten to work with your code... but it's bringing every thing from the col down an not horizontal by row ...
I have a table that has 100 col that start with Barcode barvode1 and Barcode3 .... in your code I was able to get data when I refer the col name Barcode3 for example ..an only that output ...how do I refer the other columns ? Here bit of information in the col from the data that I'm looking for they all start with atmbarcode. An not all 100 col have the data I need those start with bagbarcode...
In a nut she'll I need to look at 100 col an were the col name is Barcode does it have and entry of atmbarcode in the beginning. ...thanks for your help much appreciated
Ksharp
Super User

"I have a table that has 100 col that start with Barcode barvode1 and Barcode3 .... in your code I was able to get data when I refer the col name Barcode3 for example .."

If you have multiple variables, you can combine them all together into one variable , like the following code:

 

 

 

data have;
input x $80.;
length Bacode $ 2000;
Bacode=catx(' ',of Bacode1-Bacode100);
...........

 

Beto16
Obsidian | Level 7

Thanks for assistance it's bringing in everything for example
Atm1. Atm Atm3. Atm4.....
atmbarcode:"FE235. Row Ash atmbarcode:"Yu23

what I want output
Atm1. Atm4
atmbarcode:"FE235. atmbarcode:"Yu23

my script is
Data test;
Set test1;
Length atm $20.;
N+1
Atm=catx (' ',of atm1-atm100);
Pid=prxparse ('/atmbarcode:\d+/i');
S=1;
E=length (x);
Call prxnext (pid,s,e,x,p,l);
Do while (p>0);
Atmbarcode =substr (x,p,l);
End;
Keep n x atm1-atm600;
Run;

I thought the pid=prxparse ('/atmbarcode :\d+/i');
I thought that would filter for only that atmbarcode an nothing else?...I know what n means what is x it comes in blank ...could I get shipid into what x is



Ksharp
Super User
data have;
input x $80.;
length Bacode $ 20;
n+1;
pid=prxparse('/atmbarcode:"\w+/i');
s=1;
e=length(x);
call prxnext(pid,s,e,x,p,l);
do while(p>0);
 Bacode=substr(x,p,l);
 output;
 call prxnext(pid,s,e,x,p,l);
end;
keep n x Bacode ;
cards;
atmbarcode:"FE235. atmbarcode:"Yu23
;
run;
proc transpose data=have out=want(drop=_name_) prefix=Bacode;
by n x;
var Bacode;
run;

Beto16
Obsidian | Level 7
Hi Ksharp I added the code you provided an it brought back zeros... I thought it would bring back at least 2 since thst is how many is in the cards........am I overlooking something? Thank you again
Beto16
Obsidian | Level 7
Here is a example of my raw data I have over 500 columns that start with barcode1 thru barcode 500 ... not all columns have what I'm looking only the rows that have atmbarcode:FE235 that is just example there are other id numbers but they all start with atmbarcode:
Barcode1. Barcode2. Barcode3
Atmbarcode:TU35. Row#% atmbarcode:WE45

Here is out put
Barcode
TU35
WE45

THANK YOU
Ksharp
Super User

There are too many pattern you to consider about . Without see all your data , it is hard to write corrrect PRX .

 

 

data have;
input x $80.;
length Bacode $ 20;
n+1;
pid=prxparse('/atmbarcode:\W*\w+/i');
s=1;
e=length(x);
call prxnext(pid,s,e,x,p,l);
do while(p>0);
 Bacode=substr(x,p,l);
 output;
 call prxnext(pid,s,e,x,p,l);
end;
keep n x Bacode ;
cards;
atmbarcode:"FE235. atmbarcode:"Yu23
Atmbarcode:TU35. Row#% atmbarcode:WE45
;
run;
proc transpose data=have out=want(drop=_name_) prefix=Bacode;
by n x;
var Bacode;
run;
Beto16
Obsidian | Level 7
.my log
Numeric values have been converted to character values at the places given by (line):( column).
20658:10 20659:22 20661:15. 20663:22
Note there were 136 observation read from data set work.test. the data set want has 0 observation and 3 variables ....hope this helps as well
Beto16
Obsidian | Level 7
Hi Ksharp it worked how do i refer a table the name of table is test... an the columns names is Bacode1 thru Bacode500. How do I make these columns appear in a single col X. So that I can do substr to bacode? Thank you

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 26 replies
  • 4040 views
  • 0 likes
  • 4 in conversation