BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Beto16
Obsidian | Level 7
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



1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

13 REPLIES 13
Reeza
Super User

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

Haikuo
Onyx | Level 15

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
Obsidian | Level 7
How do I refer a file? (Named test1)
And the col where the data is located it's called x ?
Reeza
Super User

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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
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
Super User

So this is row 1 of your CSV file? 

Please explain your data?

Beto16
Obsidian | Level 7
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
Super User

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
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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