topic Re: How to get every instance of a word ? in SAS Programming

How to get every instance of a word ?

Beto16 — Fri, 21 Oct 2016 16:09:50 GMT

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

Re: How to get every instance of a word ?

Reeza — Fri, 21 Oct 2016 17:41:24 GMT

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

Re: How to get every instance of a word ?

Haikuo — Fri, 21 Oct 2016 18:20:20 GMT

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 ;

Re: How to get every instance of a word ?

Beto16 — Fri, 21 Oct 2016 19:09:57 GMT

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

Re: How to get every instance of a word ?

Reeza — Fri, 21 Oct 2016 19:25:56 GMT

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

Re: How to get every instance of a word ?

Beto16 — Fri, 21 Oct 2016 19:48:32 GMT

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

Re: How to get every instance of a word ?

Reeza — Fri, 21 Oct 2016 20:00:09 GMT

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.

Re: How to get every instance of a word ?

Beto16 — Sat, 22 Oct 2016 18:01:32 GMT

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

Re: How to get every instance of a word ?

Reeza — Sat, 22 Oct 2016 21:45:18 GMT

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.

Re: How to get every instance of a word ?

Beto16 — Sun, 23 Oct 2016 03:07:54 GMT

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

Re: How to get every instance of a word ?

Reeza — Sun, 23 Oct 2016 03:11:33 GMT

So this is row 1 of your CSV file?

Please explain your data?

Re: How to get every instance of a word ?

Beto16 — Sun, 23 Oct 2016 03:17:55 GMT

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

Re: How to get every instance of a word ?

Reeza — Sun, 23 Oct 2016 03:26:36 GMT

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.

Re: How to get every instance of a word ?

Reeza — Sun, 23 Oct 2016 05:21:47 GMT

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;