topic Re: Convert character variable to numerical (LARGE dataset, many different values) in SAS Programming

Convert character variable to numerical (LARGE dataset, many different values)

Student77 — Mon, 08 Jun 2020 02:05:46 GMT

Hello,

I have a large dataset (~30,000 obs).

I have a variable called prescriptions, and there are many many different options. In order to adjust for certain drugs in my model I need to convert these to numerical. I also need to get a count of how many of each drug there is to see if it is worth adjusting for anyway.

Is there a quick/easy way?

I would hate to go one by one:

if drug="atorvastatin" then numdrug=1;

if ...etc for hundreds of drugs.

Thank you

Re: Convert character variable to numerical (LARGE dataset, many different values)

Reeza — Mon, 08 Jun 2020 02:39:37 GMT

Depends on your data and what you're trying to do. Why do you think you need to convert them to numeric values to start off?

If the drugs are consistently named you can sort and arbitrarily number them quite easily. If they are not consistently named then you have a more complicated problem.

proc sort data=have;
by drugName;
run;

data want;
set have;

by drugName;
retain drugNum 0;

if first.drugName then drugNum+1;

run;

FYI - 30k records is not a large dataset. The number of observations doesn't change the approach in this situation, but the number of drugs will.

@Student77 wrote:

Hello,

I have a large dataset (~30,000 obs).

I have a variable called prescriptions, and there are many many different options. In order to adjust for certain drugs in my model I need to convert these to numerical. I also need to get a count of how many of each drug there is to see if it is worth adjusting for anyway.

Is there a quick/easy way?

I would hate to go one by one:

if drug="atorvastatin" then numdrug=1;

if ...etc for hundreds of drugs.

Thank you

Re: Convert character variable to numerical (LARGE dataset, many different values)

Student77 — Mon, 08 Jun 2020 03:18:31 GMT

what will this do:

retain drugNum 0;

if first.drugName then drugNum+1;

What I am trying to do is a cox proportional hazards model, and I may need to adjust for contraceptive use, hormone therapy, and menopause treatment--but in order to know if this is necessary I first need a count of how many of each drug there is, and then I need to group these.

ie group together all the types of contraceptives, and then count #contraceptives

same with hormone therapy, and the other variables I'll create.

I do not know exactly how many different values there are, but they are consistently named

Re: Convert character variable to numerical (LARGE dataset, many different values)

ChrisNZ — Mon, 08 Jun 2020 03:27:22 GMT

30,000 observations is a (very) small data set.

Without actual data examples, it's hard to give better code than @Reeza 's.

How do we know a drug is a contraceptive, and what should the output look like?

Re: Convert character variable to numerical (LARGE dataset, many different values)

Patrick — Mon, 08 Jun 2020 04:30:17 GMT

@Student77 wrote:

what will this do:
retain drugNum 0; if first.drugName then drugNum+1;

It creates a enumerator for your drugs which is what you've asked for. How this works is explained here and here.

Re: Convert character variable to numerical (LARGE dataset, many different values)

Reeza — Mon, 08 Jun 2020 14:04:47 GMT

Sounds like you'll need to recode your drugs then. Do you have a master table of which drugs belong to which groups? If so, you can either join to that table or create a format (slightly more efficient) and then do your analysis.

You can also create that table manually if you don't have it to save yourself time. That's probably the recommend route at this point.