topic Re: Regular Expressions: Performing String Extractions on Several Variables at Once in SAS Programming

Regular Expressions: Performing String Extractions on Several Variables at Once

Mgarret — Fri, 24 Jul 2015 20:00:41 GMT

I have some SAS code that applies a Regular Expression to a URL string which pulls out a Category ID.

The Variable looks like this

URL

I apply this code:

data Want; set have; category_Id=input(prxchange('s/.+categoryId=(\d+).+/$1/o',-1,URL), 12.);run;

I get this:

category_Id 61765546 62593996

The data set I have actually has many URL variables – URL_1, URL_2, URL_3….. and so on. It could go up to 80 URL vars or it could be 10.

What I need to do is pull the category IDs out of all the URL vars and then transpose them by Campaign_Name.

The data looks like this.

Campaign_Name	URL_1	URL_2	URL_3	URL_4
Summer Campaign	http://www.mywebsite.com/family/index.jsp?categoryId=61765546	http://www.mywebsite.com/shop/index.jsp?categoryId=62593996&AB=en_US_HP_S2_Men_slot_1_S2_ShopNow	http://www.mywebsite.com/family/index.jsp?categoryId=62593846&cp=1766205&ab=ln_men_cs_thetrend:tropicalprints	http://www.mywebsite.com/family/index.jsp?categoryId=62594006&cp=1766205

What I need is this:

Reeza — Fri, 24 Jul 2015 20:08:30 GMT

You'll have to tweak this a bit to deal with when a URL is missing but here's the general idea.

Declare an array to hold the url. Find the Category ID, and then output the records so it transposes in one step. Only keep the variables you need.

data want;

set have;

array url_array(80) $ url_:;

do i=1 to 80;

category_Id=input(prxchange('s/.+categoryId=(\d+).+/$1/o',-1,URL_array(i)), 12.);

OUTPUT;

end;

keep campaign_name category_id;

run;

PS if your question relates to a previous question its sometimes useful to link back to it.

EDIT: Change to correctly reference array

slchen — Fri, 24 Jul 2015 20:15:44 GMT

Try this:

category_Id=input(prxchange('s/.*(?<=categoryId=)(\d+).*/$1/',-1,string),12.);

Mgarret — Fri, 24 Jul 2015 20:21:16 GMT

That didn't seem to work for some reason. The category_id ID var came back all null. Is there something wrong with the array?

Reeza — Fri, 24 Jul 2015 20:31:57 GMT

Yes, I modified the code above already.