wide column to small

Reply
Occasional Contributor
Posts: 11

wide column to small

Hello,

I've a wide column in my dataset that contain more information that I don't need except one word of it and would like to create another column that's a subset of the wide column and keep only a few words that I'm interested like bladder. see below example.

column1

Small hiatal hernia. 8mm low-attenuation lesion in the left lobe of

the liver anteriorly (series 2, image 16) is unchanged and most likely

represents a benign cyst or hemangioma. Stable left renal cyst.

Atherosclerotic vascular calcifications including coronary artery

calcifications. Foley catheter in the bladder

Newcolumn

bladder

Many thanks!

Super User
Posts: 19,052

Re: wide column to small

Assuming that each set of words under column1 is a unique observation and that you have a list of words that you're searching for

Set up a temporary array that holds the words

Set up a second array that flags the presence of the words

Use a do loop to process the list and check for the presence of the words using the index or find function.

Occasional Contributor
Posts: 11

Re: wide column to small

Thanks Reeza for responding my question. I'm not good on array queries. Can you please explain more how to query this? Thanks

Super User
Posts: 19,052

Re: wide column to small

data want;

set have;

array words(2) _temporary_ {'bladder', 'cancer'};

array search(2) bladder cancer {2*0};

do i=1 to dim(words);

if find(string, words(i))>0 then search(i)=1;

end;

run;

Super User
Posts: 9,865

Re: wide column to small

data have;

a='Small hiatal hernia. 8mm low-attenuation lesion in the left lobe of

the liver anteriorly (series 2, image 16) is unchanged and most likely

represents a benign cyst or hemangioma. Stable left renal cyst.

Atherosclerotic vascular calcifications including coronary artery

calcifications. Foley catheter in the bladder';

b=prxchange('s/.*(\bbladder\b).*/$1/i',-1,a);

run;

PROC Star
Posts: 1,146

Re: wide column to small

Here's an example of how to solve this problem with a number of approaches, including one from our friend

http://support.sas.com/resources/papers/proceedings14/1717-2014.pdf

The original thread is titled `Scanning an Observation for a Word within a Variable`

Tom

Super User
Posts: 9,865

Re: wide column to small

Smiley Happy Thank you to mention me . My real name is Xia Ke Shan . Ksharp is my nickname because I love CS and A member from 3D team which name is Ksharp (I steal it from him) is who I really respected, His sharpshooter skill is really what I want .

Super User
Super User
Posts: 7,695

Re: wide column to small

Just out of interest, what is the reasoning behind this.  If I was looking at this kind of information I would want to have it coded by a professional using standards such as MedDRA, rather than trying to find words in a string.  Even accounting for spellings might get tricky, let alone medical terminolgy of things.

Ask a Question
Discussion stats
  • 7 replies
  • 252 views
  • 0 likes
  • 5 in conversation