Help using Base SAS procedures

Altering var values across the entire dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Altering var values across the entire dataset

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?


Accepted Solutions
Solution
‎11-10-2014 09:16 AM
Super User
Super User
Posts: 7,432

Re: Altering var values across the entire dataset

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;

     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.

View solution in original post


All Replies
Solution
‎11-10-2014 09:16 AM
Super User
Super User
Posts: 7,432

Re: Altering var values across the entire dataset

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;

     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.

Occasional Contributor
Posts: 13

Re: Altering var values across the entire dataset

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

Super User
Posts: 9,691

Re: Altering var values across the entire dataset

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 172 views
  • 0 likes
  • 3 in conversation