topic Re: Efficiently organizing multiple levels of a categorical variable into a single numeric variable in New SAS User

Efficiently organizing multiple levels of a categorical variable into a single numeric variable

EmilyAV — Thu, 12 Aug 2021 15:06:39 GMT

Hello,

I have a long list of entries for a categorical variable classified into levels in a RTF document.

What is the most efficient way to reclassify each observation into a new numeric variable? There is a lot of variation in punctuation between apparently similar observations, which I've classified manually.

Sample code, which works, but is untenable for hundreds of individual iterations of each level...

data new;
set old;
length fruit_num 8.;
if fruit = 'Apple' or fruit = 'Apples' or fruit = 'apple' then fruit_num = 1;

if fruit = 'Orange' or fruit = 'Oranges' or fruit = ' organge' then fruit_num = 2;

else fruit_num = .;

run;

Re: Efficiently organizing multiple levels of a categorical variable into a single numeric variable

Reeza — Thu, 12 Aug 2021 15:30:13 GMT

Make your RTF file an Excel file with one column having the original value and the second column having the new value.
Then merge your two tables together. You can help yourself do some of this programatically by also standardizing the values in some manner - ie ensuring all are lower case, remove 's' at the end to at least get started.

You can also use the IN operator:

if upcase(fruit) in: ('APPLE') then fruit_num=1;

Anything that starts with APPLE (regardless of case) will now get classified as fruit_num=1, even APPLEPIE. Note the colon and IN usage in that statement.
It will not capture lines like Organge though.

Re: Efficiently organizing multiple levels of a categorical variable into a single numeric variable

Ksharp — Fri, 13 Aug 2021 12:31:01 GMT

Make a mapping dataset like:

Apple 1
apples 1
applle 1
.........

and using Hash Table to solve it .

Re: Efficiently organizing multiple levels of a categorical variable into a single numeric variable

EmilyAV — Mon, 16 Aug 2021 12:05:24 GMT

Works great, thank you!