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?
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.
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.
Thank you. This worked like a charm straight away, not even a typo.
Why not use proc format + cntlin to make a informat ? or Hash Table easy?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.