DATA Step, Macro, Functions and more

How to get every instance of a word ?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

How to get every instance of a word ?

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




Accepted Solutions
Solution
‎10-21-2016 02:42 PM
Super User
Posts: 19,822

Re: How to get every instance of a word ?

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

View solution in original post


All Replies
Solution
‎10-21-2016 02:42 PM
Super User
Posts: 19,822

Re: How to get every instance of a word ?

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

Respected Advisor
Posts: 3,156

Re: How to get every instance of a word ?

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

Re: How to get every instance of a word ?

How do I refer a file? (Named test1)
And the col where the data is located it's called x ?
Super User
Posts: 19,822

Re: How to get every instance of a word ?

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

Frequent Contributor
Posts: 80

Re: How to get every instance of a word ?

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

Re: How to get every instance of a word ?

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. 

Frequent Contributor
Posts: 80

Re: How to get every instance of a word ?

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

Re: How to get every instance of a word ?

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. 

Frequent Contributor
Posts: 80

Re: How to get every instance of a word ?

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

Re: How to get every instance of a word ?

So this is row 1 of your CSV file? 

Please explain your data?

Frequent Contributor
Posts: 80

Re: How to get every instance of a word ?

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





Super User
Posts: 19,822

Re: How to get every instance of a word ?

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. 

Super User
Posts: 19,822

Re: How to get every instance of a word ?

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;

 

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 880 views
  • 0 likes
  • 3 in conversation