topic Re: Create variables from multiple character text in SAS Programming

Create variables from multiple character text

stancemcgraw — Thu, 29 Oct 2020 16:41:47 GMT

Hi all,

I have a variable called drugs found, and it's in character text with a lot of text per line. I'd like a count of each type of drug found per ID.

Data have;

studyid drugs_found

5 Methamphine, amphetamines, cocaine

6 none

7 THC

data want;

studyid Methamphine Cannabis Amphetamines Cocaine No_drugs

5 1 0 1 1 0

6 0 0 0 0 1

7 0 1 0 0 0

Re: Create variables from multiple character text

PaigeMiller — Thu, 29 Oct 2020 17:11:46 GMT

I don't see "Cannabis" in the input data.

Also, are these drugs in the input data always going to be spelled identically each time? Do you know in advance what the possible drugs are, or does the program have to figure it out? Does the final table always have values zero and 1, or could there be integers 2, 3, ...?

Re: Create variables from multiple character text

stancemcgraw — Thu, 29 Oct 2020 17:17:11 GMT

It says THC in the input data, thus I wanted it recorded as CANNABIS and yes, I'd like it to be recorded as dummy variables with 0 and 1.

I included a screenshot of what it looks like, because it does not always look the same and some of the words are cut-off.

Re: Create variables from multiple character text

PaigeMiller — Thu, 29 Oct 2020 17:19:33 GMT

@stancemcgraw wrote:

It says THC in the input data, thus I wanted it recorded as CANNABIS and yes, I'd like it to be recorded as dummy variables with 0 and 1.

I included a screenshot of what it looks like, because it does not always look the same and some of the words are cut-off.

Ok, thank you, but I asked other questions that you didn't answer.

Re: Create variables from multiple character text

stancemcgraw — Thu, 29 Oct 2020 17:23:46 GMT

I don't understand what you mean by, do I know in advance what the possible drugs are? And no, they will not be spelled the exact same way each time. Take a look at the screenshot and you can see all the different iterations...

Re: Create variables from multiple character text

Reeza — Thu, 29 Oct 2020 18:22:14 GMT

1. Split into a long format using SCAN()
2. Use previously shown methods, from your recent questions, to transpose and create your indicators.

data have;
infile cards dlm='09'x;
length drugs_found $50.;
input studyid  $    drugs_found $;
cards;
5	Methamphine, amphetamines, cocaine
6	none
7	THC
;;;;
run;


data long;
set have;

nTerms = countw(drugs_found) ;

do i=1 to nTerms;
word = scan(drugs_found, i, ",");
put word;
if word in ("THC", "CBD") then word = "Cannabis";
else if word in ("none") then call missing(word);
output;
end;

run;

Now use the similar methods shown. If you need to recode values, ie THC/CBD to Cannabis you can add that in the data step above and then transpose your data.

Re: Create variables from multiple character text

stancemcgraw — Thu, 29 Oct 2020 18:35:35 GMT

Thank you I always forget the going from wide to long step, you're right. You've really helped my day, thanks so much!