Help using Base SAS procedures

How to extract data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

How to extract data

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


Accepted Solutions
Solution
‎06-23-2016 02:31 PM
Super User
Posts: 10,023

Re: How to extract data

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


All Replies
Super User
Posts: 5,503

Re: How to extract data

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.

Frequent Contributor
Posts: 80

Re: How to extract data

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.
Super User
Posts: 19,789

Re: How to extract data

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.

Super User
Posts: 5,503

Re: How to extract data

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

 

Super User
Posts: 10,023

Re: How to extract data

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;
Frequent Contributor
Posts: 80

Re: How to extract data

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
Super User
Posts: 10,023

Re: How to extract data

"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);
...........

 

Frequent Contributor
Posts: 80

Re: How to extract data


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



Solution
‎06-23-2016 02:31 PM
Super User
Posts: 10,023

Re: How to extract data

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;

Frequent Contributor
Posts: 80

Re: How to extract data

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
Frequent Contributor
Posts: 80

Re: How to extract data

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
Super User
Posts: 10,023

Re: How to extract data

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;
Frequent Contributor
Posts: 80

Re: How to extract data

.my log
Numeric values have been converted to character values at the places given by (line)Smiley Sad 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
Frequent Contributor
Posts: 80

Re: How to extract data

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
☑ This topic is solved.

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

Discussion stats
  • 26 replies
  • 644 views
  • 0 likes
  • 4 in conversation