topic Altering var values across the entire dataset in SAS Procedures

Altering var values across the entire dataset

Dess — Mon, 10 Nov 2014 14:06:17 GMT

I have a dataset dsEvents which contains multiple variables/attributes of the same value domain. I would like to change these values to arbitrary ranking numbers.

Consider the dataset:

data dsEvents;
     input ID e1 e2 e3 e4 e5 e6 e7 e8 e9 e10 e11;
     
     cards;
     077123 A A4 B I1 L L U8 A E2 A3 W5;
     622941 B B B2 L L5 C9 E1 A L Q D9;
     2452 E E B A4 B2 O C L Q D9 W2;

     run;

I want to swap these values to numbers according to some arbitrary ranking system:

A = -1
A4 = 0
L = 0
W5 = 22
B = 2
E = 3

...etc

Yielding as a result (removed latter attributes for brevity)

data dsRanking;
     input ID e1 e2 ...;
     
     cards;
     077123 -1 0 ...;
     622941 2 2 ...;
     2452 3 3 ...;

     run;

My current understanding is to use a regexp prxchange(s///g) for every var and for every possible value. This is a lot.

Thus, I ask for your assistance in finding a better way. Keep in mind that every value can appear in every variable/attribute. There are about 16 possible values, and up to 50 variables/attributes.

Any idea how to solve this?

Re: Altering var values across the entire dataset

RW9 — Mon, 10 Nov 2014 14:16:55 GMT

You could use an array, and a select:

data want;

set have;

array e{11};

do I=1 to 11;

select(e{I});

when ('A') e{I}='1';

when ('A4' e{I}='0';

...

end;

run;

Its pretty clean. Alternatively put your conditions in a table then use merging techniques to merge the lookup table onto your data and replace.

Re: Altering var values across the entire dataset

Ksharp — Mon, 10 Nov 2014 14:46:28 GMT

Why not use proc format + cntlin to make a informat ? or Hash Table easy?

Re: Altering var values across the entire dataset

Dess — Fri, 14 Nov 2014 11:09:49 GMT

Thank you. This worked like a charm straight away, not even a typo.