How to get every instance of a word ?

[Beto16]

Hi have a data dump that has a special word in the comment field I need to extract every instance.

Here is the script that I was using

Data have;
Infile 'C:\users\desktop \night4.csv' dlm ='09' X truncover;
Input x $25000.;
Length Bacode $25;
N+1;
Pid=prxparse ('/atmbarcode:\W*\w+/i');
s=1;
E=length (x);
Call prxnet (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;
Run;

Here is the data it's 3 cols

A
"Atmbarcode ":"CA74822531", Atmbarcode ":"AZ74822531", Atmbarcode ":"WA74822531", ... Everytime there is a atmbarcode I need the 10 characters to be extract ...
Out put
X
AZ74822531
WA74822531
CA74822531

Thanks

[Reeza · Accepted solution]

 Are there other words in the series? Please make sure the sample is representative of your actual data. 

[Haikuo]

If read in as raw dump, you could also try:

filename FT15F001 temp lrecl=512; 
data test;
infile FT15F001 truncover scanover ;
input  @"Atmbarcode " +3 var $10. @@;
parmcards4;
"Atmbarcode ":"CA74822531", Atmbarcode ":"AZ74822531", Atmbarcode ":"WA74822531", 
;;;;
run;

filename ft15f001 clear ;

[Beto16]

How do I refer a file? (Named test1)
And the col where the data is located it's called x ?

[Reeza]

I definitely don't think mine is the correct answer.

Also, I could have sworn you asked this question once already and received several solutions....

[Beto16]

Yes I did but the raw data change before I had 100 col to extract an now the data is in one cell which could have 10 to 20 I'd I would need to extract... the solution I was giving doesn't work anymore

[Reeza]

Ok.

As I mentioned before, how close is this to your exact data. 

Post several records that show the variation in your data. If it's ALL structured the way you've specified, you can use SCAN and a do loop to extract all the records. 

[Beto16]

the data looks
[{"row":1,"bagbarcode ":"RS5748008","Atmbarcode ":"CA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748009","Atmbarcode ":"TX797220129","$$HASHKEY":"object:14"}] this is in one cell sometimes there can be 10 entries in that cell I need data to look
Output
A. B
TX797220129. RS5748008
CA797220129. RS5748009

[Reeza]

Hate to repeat myself but...

Post several records that show the variation in your data.

Also, is the original file JSON or XML? It looks sort of like that format. 

[Beto16]

The file comes in as a check csv. I open thru excel ..

Row 1
row":1,"bagbarcode ":"RS5748008","Atmbarcode ":"CA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748009","Atmbarcode ":"TX797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748015","Atmbarcode ":"WV0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748029","Atmbarcode ":"SA97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748028","Atmbarcode ":"CA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS57480259","Atmbarcode ":"JQ797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748039","Atmbarcode ":"NY0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748038","Atmbarcode ":"QU97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748048","Atmbarcode ":"UT0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748048","Atmbarcode ":"DA97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748058","Atmbarcode ":"DF797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748016","Atmbarcode ":"JX797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS57480017","Atmbarcode ":"QA0797220118","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748019","Atmbarcode ":"FL797220129","$$HASHKEY":"object:14"}, row":1,"bagbarcode ":"RS5748012","Atmbarcode ":"LA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748011","Atmbarcode ":"WA797220129","$$HASHKEY":"object:14"}


This is an example of my raw data .. I would need help with setting up a scan with loop ..thank you

[Reeza]

So this is row 1 of your CSV file? 

Please explain your data?

[Beto16]

The data is in one cell BUT data is format like this

Raw data. Date. Id
Raw data. 10/01/16. 1ZR67

RAW DATA 10/06/16. 1ZR68

[Reeza]

Maybe you should post a small sample of the file. 

Make sure to include multiple lines and all other fields. 

And save the CSV as a txt file to attach it. 

[Reeza]

Here's a tested version:

data have;
	length field $2000.;
	informat field $2000.;
	field='{"row":1,"bagbarcode ":"RS5748008","Atmbarcode ":"CA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748009","Atmbarcode ":"TX797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748015","Atmbarcode ":"WV0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748029","Atmbarcode ":"SA97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748028","Atmbarcode ":"CA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS57480259","Atmbarcode ":"JQ797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748039","Atmbarcode ":"NY0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748038","Atmbarcode ":"QU97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748048","Atmbarcode ":"UT0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748048","Atmbarcode ":"DA97220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS5748058","Atmbarcode ":"DF797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748016","Atmbarcode ":"JX797220129","$$HASHKEY":"object:14"},row":1,"bagbarcode ":"RS57480017","Atmbarcode ":"QA0797220118","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748019","Atmbarcode ":"FL797220129","$$HASHKEY":"object:14"}, row":1,"bagbarcode ":"RS5748012","Atmbarcode ":"LA0797220129","$$HASHKEY":"object:14"},{"row":1,"bagbarcode ":"RS5748011","Atmbarcode ":"WA797220129","$$HASHKEY":"object:14"}';
run;

data want;
	set have;
	
	*Number of key-value pairs;
	x=countw(field, ",");

    *Loop over pairs;
	do i=1 to x;
	   
	    *Get the key-value pair;
		code=scan(field, i, ",");

        *Search for code of interest;
		if find(code, 'Atmbarcode')>0 then
			do;
			    *Extract components required;
				key=compress(scan(code, 1, ":"), '"');
				value=compress(scan(code, 2, ":"), '"');
				
				*Print results to data set;
				output;
			end;
	end;
	keep key value;
run;