topic Re: How to extract data in SAS Procedures

How to extract data

Beto16 — Mon, 13 Jun 2016 20:11:05 GMT

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

Re: How to extract data

Astounding — Mon, 13 Jun 2016 21:03:59 GMT

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.

Re: How to extract data

Beto16 — Tue, 14 Jun 2016 00:58:58 GMT

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.

Re: How to extract data

Reeza — Tue, 14 Jun 2016 02:38:44 GMT

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.

Re: How to extract data

Ksharp — Tue, 14 Jun 2016 03:27:14 GMT

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;

Re: How to extract data

Astounding — Tue, 14 Jun 2016 14:07:01 GMT

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/cdl//en/lefunctionsref/67960/HTML/default/p0jshdjy2z9zdzn1h7k90u99lyq6.htm

Re: How to extract data

Beto16 — Thu, 16 Jun 2016 22:32:18 GMT

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

Re: How to extract data

Ksharp — Fri, 17 Jun 2016 00:44:29 GMT

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

Re: How to extract data

Beto16 — Fri, 17 Jun 2016 19:23:52 GMT

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

Re: How to extract data

Ksharp — Sat, 18 Jun 2016 07:06:20 GMT

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;

Re: How to extract data

Beto16 — Wed, 22 Jun 2016 18:10:08 GMT

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

Re: How to extract data

Beto16 — Wed, 22 Jun 2016 18:22:33 GMT

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

Re: How to extract data

Beto16 — Wed, 22 Jun 2016 18:45:36 GMT

.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

Re: How to extract data

Ksharp — Thu, 23 Jun 2016 00:56:28 GMT

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;

Re: How to extract data

Beto16 — Thu, 23 Jun 2016 02:32:33 GMT

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

Re: How to extract data

Ksharp — Thu, 23 Jun 2016 02:41:57 GMT

data test;
infile cards truncover;
input (Bacode1-Bacode3) (: $40.);
cards;
atmbarcode:"FE235. atmbarcode:"Yu23
Atmbarcode:TU35. Row#% atmbarcode:WE45
;
run;

data have;
 set test;
length x $ 32767;
x=catx(' ',of Bacode1-Bacode3);
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 ;

proc transpose data=have out=want(drop=_name_) prefix=Bacode;
by n x;
var Bacode;
run;

Re: How to extract data

Beto16 — Thu, 23 Jun 2016 03:13:12 GMT

Hi Ksharp I copy your code an refer Test table an prxparse atmbarcode: an did the the catx to include only the columns I want to select even though it'sbe 600 ...an it comes back when I use cards it works beautiful. ..when I try to refer table it fail. ..i really appreciate your help

Re: How to extract data

Beto16 — Thu, 23 Jun 2016 03:13:40 GMT

Do you see anything I might be missing?

Re: How to extract data

Ksharp — Thu, 23 Jun 2016 03:40:24 GMT

Your Error Log information ?

Post a TEXT/CSV file contains your TEST data.

Run PROC CONTENTS DATA=TEST;RUN; and post the result.

Re: How to extract data

Beto16 — Thu, 23 Jun 2016 04:11:25 GMT

The sas system
The contents procedure
Data set name work.test. observation 136
Member name Data. Variables. 255
engine. V9. Indexes. 0
Created.06/22/16. 22:45:5 Observations length 5456
last modified 06/22/16 22:45:5. Deleted observation 0
Protection. Compress No
Data set type. Sorted no
Label.
Data representation win_32
Encoding latinwestern

engine /host dependant information

Data set page size. 262144
Number of data set pages 3
First data page 1
Max obs per page 48
Obs in first data page 42
Number of data set repair 0
Extendobs counter. Yes
Filename test.sas7dat
Release 9.0401m3
Host created w32_7pro

Alphabetical list of variables an attributes

# variable. Type. Len. Format informat label
3. Atm1. Char. 22. $22. $22. Atm1
4. Atm2. Char. 25. $25. $25. ATM2
5. Atm3. Char. 24. $24. , $24. Atm3
6. Atm4. Char. 19. . $19. $19. Atm4