topic Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character in New SAS User

Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Wed, 13 Mar 2019 02:35:12 GMT

Hey all! I'm pretty much a novice, so anything out of the ordinary requires me to call on the help of others, which is what I'm doing.

What I want to do is to take a piece of html source code like I have below, and "clean" it such that all that remains is a list of keywords/hashtags. What I have going for me is that (for all intents and purposes) you can be pretty sure that the "#" acts as a faithful indicator that there's a keyword to follow. Any help will be greatly appreciated.

description available."}},{"node":{"comments_disabled":false,"__typename":"GraphImage","id":"1998217523958621257","edge_media_to_caption":{"edges":[{"node":{"text":"Summer is coming! \u2600\ufe0f \ud83c\udf1e\n#Freude #FreudeCrewMember #FreudeAmKFZ #VWJetta #mk2 #volkswagen #AudiS3 #Audi #VWAudi #VAG #lowered #boost #orange #red #car #tuning #summer #comingsoon #carmeet #seasoniscoming #driving #carsarelife #static @red_jetta_1.8_t"}}]},"shortcode":"Bu7GEBDg6RJ","edge_media_to_comment":{"count":0},"taken_at_timestamp":1552426113,"dimensions":{"height":1080,"width":1080},"display_url":"https://scontent-lax3-1.cdninstagram.com/vp/f5d30fea48bee4d2492d8fa8d28922f2/5D15DD26/t51.2885-15/e35/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","edge_liked_by":{"count":10},"edge_media_preview_like":{"count":10},"owner":{"id":"11297619899"},"thumbnail_src":"https://scontent-lax3-1.cdninstagram.com/vp/08ae6f6b7cac751844513bb5e5730fb3/5D1955C3/t51.2885-15/sh0.08/e35/s640x640/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","thumbnail_resources":[{"src":"https://scontent-lax3-1.cdninstagram.com/vp/0e3d9a608cf07804893534062e8ae3fb/5D172664/t51.2885-15/e35/s150x150/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":150,"config_height":150},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/03375eff9b3fc793c2eafc77ae4d1087/5D10FB2E/t51.2885-15/e35/s240x240/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":240,"config_height":240},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/6322eb6c96db8f7cd2852510909ef475/5D219E94/t51.2885-15/e35/s320x320/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":320,"config_height":320},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/ad83b93d7dab9e019b32472320b5cc65/5D09F7CE/t51.2885-15/e35/s480x480/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":480,"config_height":480},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/08ae6f6b7cac751844513bb5e5730fb3/5D1955C3/t51.2885-15/sh0.08/e35/s640x640/53786494_357691698290379_8067023115557724894_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":640,"config_height":640}],"is_video":false,"accessibility_caption":"Image may contain: car and outdoor"}},{"node":{"comments_disabled":false,"__typename":"GraphImage","id":"1998201676453166113","edge_media_to_caption":{"edges":[{"node":{"text":"Ferrari FF #cars #carporn #carphotography #topcars #coolcars #automotivephography #automotivephographer #autogespot #amazing_cars #instacars #instacars247 #carsarelife #carphoting #supercarsdaily #supercars247 #supercarsdaily700 #carspotting #carspotter"}}]},"shortcode":"Bu7CdZ6lLwh","edge_media_to_comment":{"count":0},"taken_at_timestamp":1552424223,"dimensions":{"height":1080,"width":1080},"display_url":"https://scontent-lax3-1.cdninstagram.com/vp/bd6284b185ea7effbe3e5600d31cebca/5D0A5644/t51.2885-15/e35/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","edge_liked_by":{"count":12},"edge_media_preview_like":{"count":12},"owner":{"id":"11657938037"},"thumbnail_src":"https://scontent-lax3-1.cdninstagram.com/vp/65a41539f298087294633eb9468befe6/5D0674A1/t51.2885-15/sh0.08/e35/s640x640/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","thumbnail_resources":[{"src":"https://scontent-lax3-1.cdninstagram.com/vp/cc5a4aa3f60c8243cf537a788a21562b/5D1B9C06/t51.2885-15/e35/s150x150/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":150,"config_height":150},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/d243b9483f25106e203c538fa84bc4df/5D1ACE4C/t51.2885-15/e35/s240x240/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":240,"config_height":240},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/0a65c76e5938c677fee8e4625ea83d0b/5D1CDDF6/t51.2885-15/e35/s320x320/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":320,"config_height":320},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/eaaeb4aac8c2f0ba9f4fe2d35b1d27a4/5D0659AC/t51.2885-15/e35/s480x480/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":480,"config_height":480},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/65a41539f298087294633eb9468befe6/5D0674A1/t51.2885-15/sh0.08/e35/s640x640/52543684_432358277570476_3435469715500693989_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":640,"config_height":640}],"is_video":false,"accessibility_caption":"Image may contain: car"}},{"node":{"comments_disabled":false,"__typename":"GraphVideo","id":"1998184571132976705","edge_media_to_caption":{"edges":[{"node":{"text":"Car is still his favourite word but at least he\u2019s throwing some colours in as well now \ud83d\ude02"}}]},"shortcode":"Bu6-kfWDB5B","edge_media_to_comment":{"count":4},"taken_at_timestamp":1552422264,"dimensions":{"height":750,"width":750},"display_url":"https://scontent-lax3-1.cdninstagram.com/vp/59ea755e6d8892326277c6bff273591e/5C8ADCC0/t51.2885-15/e15/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","edge_liked_by":{"count":20},"edge_media_preview_like":{"count":20},"owner":{"id":"7112182"},"thumbnail_src":"https://scontent-lax3-1.cdninstagram.com/vp/32e320a234c664f6f5b217961188d229/5C8A6C3E/t51.2885-15/e15/s640x640/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","thumbnail_resources":[{"src":"https://scontent-lax3-1.cdninstagram.com/vp/1c6fc3793741f0e27deea7fa9dcec1c2/5C8A53E1/t51.2885-15/e15/s150x150/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":150,"config_height":150},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/b27a2d0567b421fc68e5e12b1a3ba2aa/5C8AF36B/t51.2885-15/e15/s240x240/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":240,"config_height":240},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/1e9cdc399524e00fc52b5aa284fdbc99/5C8B8B11/t51.2885-15/e15/s320x320/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":320,"config_height":320},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/39874c4e5cb8e97e49072d460d422df0/5C8A6C8B/t51.2885-15/e15/s480x480/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":480,"config_height":480},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/32e320a234c664f6f5b217961188d229/5C8A6C3E/t51.2885-15/e15/s640x640/51820254_395521411001257_3011032355330220914_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":640,"config_height":640}],"is_video":true,"video_view_count":59}},{"node":{"comments_disabled":false,"__typename":"GraphImage","id":"1998169659401479808","edge_media_to_caption":{"edges":[{"node":{"text":"Is Adobe Photoshop really worth it for pictures?\ud83e\udd14 #taillighttuesday \ud83d\udcf8 \u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\nCar Mods \ud83d\udd27 \n@corsaperformance | @jltperformance\n@diodedynamics | @pmas.maf \n@luxeautoconcepts | @lund_racing\n@fordperformance\n\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\nSponsors \u00a9\ufe0f\n@carlock.co | @lightingtrendz @ace_auto_detailing \u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\u25ab\ufe0f\nHashTags #\n#fordperformance #performancepackage #s550 #photography #carcommunity #fordmustanggt #brembo #carsarelife #mustangfanclub #mustangclubest #diodedynamics #mustangfanclub #s197 #mustangnation #fastlife #mustangjuice #mustang_everyday #mustangsociety #mustangsofinstagram #carporn #corsaexhaust #stanggang #mustangaddicts #fordracing #mustang_lifestyle\n\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\u25aa\ufe0f\nCredits \ud83d\udcf8 @icyy_cali (iPhone XS Max)"}}]},"shortcode":"Bu67LftlqaA","edge_media_to_comment":{"count":4},"taken_at_timestamp":1552420407,"dimensions":{"height":1350,"width":1080},"display_url":"https://scontent-lax3-1.cdninstagram.com/vp/029125e9463a896cee5c1ff2b74cdafb/5D290065/t51.2885-15/e35/p1080x1080/52762717_121260398971453_4664182291038734575_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","edge_liked_by":{"count":87},"edge_media_preview_like":{"count":115},"owner":{"id":"509207344"},"thumbnail_src":"https://scontent-lax3-1.cdninstagram.com/vp/ee8ead928fd617283b3ecfc5290c46b5/5D26BAF6/t51.2885-15/sh0.08/e35/c0.180.1440.1440a/s640x640/52762717_121260398971453_4664182291038734575_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","thumbnail_resources":[{"src":"https://scontent-lax3-1.cdninstagram.com/vp/7de95911fb39b4b53b4d0343e1874bf9/5D09853D/t51.2885-15/e35/c0.180.1440.1440a/s150x150/52762717_121260398971453_4664182291038734575_n.jpg?_nc_ht=scontent-lax3-1.cdninstagram.com","config_width":150,"config_height":150},{"src":"https://scontent-lax3-1.cdninstagram.com/vp/a74615dc2821a069e1cdbfdbde2f24a3/5D288C77/t51.2885-

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

PGStats — Wed, 13 Mar 2019 03:11:02 GMT

Here is one way of doing this. It does require reading the whole string into a character variable:

data have;
infile "&sasforum.\datasets\obscure.txt" truncover;
input str $10000.;
run;

data want;
if not prxId then prxId + prxParse("/#\w+/o");
set have;
length keyword $20;
start = 1;
stop = length(str);
call prxnext(prxId, start, stop, str, pos, len);
do while (pos > 0);
    keyword = substr(str, pos, len);
    output;
    call prxnext(prxId, start, stop, str, pos, len);
    end;
keep keyword;
run;

proc print data=want; run;

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Wed, 13 Mar 2019 05:57:34 GMT

Thanks for the reply.

This looks like it is working as it should, EXCEPT it is only returning 35 results, and I know there to be about 1200.

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Wed, 13 Mar 2019 06:36:22 GMT

I looked through the HAVE file and note that there are only 35 hashtags that make it in there, even though it captures much of the string before and after the large missing chunk of string.

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

PGStats — Wed, 13 Mar 2019 15:54:54 GMT

Attached is the text file that I processed... 67 #hashtags were found.

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Wed, 13 Mar 2019 21:03:12 GMT

I think the problem is that my .txt file includes HTML markup that isn't present in your .txt file (my example you copied was a snippet from the middle of the source code, whereas my .txt file includes all of the code).

When i use your .txt file I get the same results as you, and a HAVE file with one cell. When I use my .txt file I get bad results, and a HAVE file with hundreds of observations.

I am trying to figure out a way to give you the code I'm working with, but it can't attach as a file ("the contents of the attachment doesn't match its file type") and it's too long to insert as code into the body of this message.

Edit: attaching as a word document

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

PGStats — Thu, 14 Mar 2019 03:33:32 GMT

I put your html code from the docx file back into a text form and read it using this:

data have2;
infile "&sasforum.\datasets\obscure2.txt" lrecl=300000 flowover dlm=" \";
input @"#" keyword :$20. @@;
pos = notname(keyWord);
if pos > 0 then keyword = substrn(keyword,1,pos-1);
if not missing(keyword);
drop pos;
run;

I extracted 784 hashtags into dataset have2.

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Thu, 14 Mar 2019 05:02:50 GMT

This worked great!

In words (without wasting too much of your time) can you tell me what you did differently to create have2 than you did to create have?

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

PGStats — Thu, 14 Mar 2019 05:25:51 GMT

It is completely different. This new version uses the @'value' feature of the input statement to seek words following # characters repeatedly. This overcomes the length limit of character constants (32Kb, while max lrecl is 1Gb). Strings that are read this way are then trimmed of any stray characters using function notname which finds the first char that is not alphanum or underscore.

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

acemanhattan — Thu, 14 Mar 2019 05:34:33 GMT

Okay, got it!

The only surprise I'm finding is that it seems to be case sensitive, so it reads #xxx as different than #XXX, which means that if I do a count on the different values that show up in have2, I'll get 2 observations with counts for #xxx and #XXX, when they're really a single observation for my purposes. Would we just read the string in using some function that applies either capitalization or lowercase to every character, and is there a benefit of choosing one over the other?

Re: Reading In Obscure Data, Only Keep Data That Follows Specific Character

PGStats — Thu, 14 Mar 2019 15:26:26 GMT

Just add

keyword = lowcase(keyword);

or whatever. Then sort with NODUPKEY option to eliminate duplicates.