Solved
Contributor
Posts: 24

# Data Manipulate

Dear Experts,

I got a situation. It is about body part injury and admission data. H being head, C being chest, A abdomen etc. 6 different body parts, and 1, 2, 3, and 4 being severity codes 1 being mild and 4 being severe. Each row is a case (or say admission to health center). These letter and number codes are combined together and has 20,000+ cases. I would like to select one letter code with highest severity code (number) and make one column say MaxBI (body part with maximum injury). Some of the cases don't have all 6 body injuries. Following is what I have:

Data have;

input \$ BI1-BI6;

cards;

H1   C2   F4   A2   G1   E2

H3   C1   F2   A4   G2   E1

C2   F3          G4   E2

H4          F1                  E3

...

;

Data Want: /*with MaxBI*/

H1   C2   F4   A2   G1   E2         F4

H2   C1   F2   A3   G2   E1         A3

C2   F1          G3   E2         G3

H2          F1                  E1         H2

...

I could split the letters and numbers from the first six columns using Substring function and take the Max of the substringed values, but I would not know which body part it came from. That is where I got stuck.

I would appreciate very much for your kind help.

Thank you.

Sijansap

Accepted Solutions
Solution
‎10-12-2017 12:17 PM
PROC Star
Posts: 274

## Re: Data Manipulate

I think this does what you want:

```data want;
set have;
array BI BI1-BI6;
array severity(6) 8 _temporary_;
do _N_=1 to 6;
severity(_N_)=input(substr(BI(_N_),2),?? 1.); /* convert to numbers */
end;
_N_=whichn(max(of severity(*)),of severity(*)); /* Find first pos. of maximum */
MaxBI=BI(_N_);
run;```

All Replies
Super User
Posts: 23,980

## Re: Data Manipulate

You'll need to separate them. I would suggest two arrays, one with the numbers and one with the letters.

Then you can find the largest and whatever index it has will also be the corresponding body part.

Contributor
Posts: 24

## Re: Data Manipulate

Hi Reeza,

Thanks for the suggestion. I am not sure how I can make the two arrays you mentioned. I can make array for numbers, but not sure for the letters and to attach the highest number to the exact letter that came with originally. I apologize for being novice in array method.

I would appreciate if you could give me a bit more hint for the array layout for this awkward problem I am having.

Thank you,

Sijansap

Super User
Posts: 23,980

## Re: Data Manipulate

1. Create an array for your original variables.

2. Create two other arrays, one for the letter, one for the numbers.

3. Loop through and separate it using SUBSTR()

Once you've got that working, I can show you how to get the max with body part.

sijansap wrote:

Hi Reeza,

Thanks for the suggestion. I am not sure how I can make the two arrays you mentioned. I can make array for numbers, but not sure for the letters and to attach the highest number to the exact letter that came with originally. I apologize for being novice in array method.

I would appreciate if you could give me a bit more hint for the array layout for this awkward problem I am having.

Thank you,

Sijansap

Occasional Contributor
Posts: 14

## Re: Data Manipulate

Try this:

Data have;
input (BI1-BI6) (\$);
cards;
H1 C2 F4 A2 G1 E2 F4
H2 C1 F2 A3 G2 E1 A3
C2 F1 G3 E2 G3 .  .
. . . H2 F1 E1 H2
;;;

data want;
set have;
array aa {*} BI: ;
max= 0;
do i = 1 to dim(aa);
num = input(compress(aa{i},'1234567890','k'),20.) ;
if num >=  max then do;
idx = i;
max = num;
end;
end;
result = aa{idx};

* drop max idx;
Drop i;
run;

Solution
‎10-12-2017 12:17 PM
PROC Star
Posts: 274

## Re: Data Manipulate

I think this does what you want:

```data want;
set have;
array BI BI1-BI6;
array severity(6) 8 _temporary_;
do _N_=1 to 6;
severity(_N_)=input(substr(BI(_N_),2),?? 1.); /* convert to numbers */
end;
_N_=whichn(max(of severity(*)),of severity(*)); /* Find first pos. of maximum */
MaxBI=BI(_N_);
run;```
Frequent Contributor
Posts: 123

## Re: Data Manipulate

This could also be done using the reverse function and call sortc.

``````data want(drop=reverse:);
set have;
reverse_bi1=reverse(bi1);
reverse_bi2=reverse(bi2);
reverse_bi3=reverse(bi3);
reverse_bi4=reverse(bi4);
reverse_bi5=reverse(bi5);
reverse_bi6=reverse(bi6);
call sortc(of reverse_bi1-reverse_bi6);
maxbi=reverse(reverse_bi6);
run;``````
Contributor
Posts: 24

## Re: Data Manipulate

Thank you all for your kind help in solving my problem.  Reeza's guide to array was educational. A bit long but PSNn's solution worked just fine. I also liked the simplified array by s_lassen. The easiest to follow was with reverse function by SuzanneDorinski.

Again thank you all, I appreciate your time and help.

Sijansap

☑ This topic is solved.