Data Manipulate

[sijansap]

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

[Reeza]

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. 

[sijansap]

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

[Reeza]

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. 

Post your code. 

---

@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

---

[PSNn]

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;

[s_lassen · Accepted solution]

@sijansap:

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;

[SuzanneDorinski]

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;

[sijansap]

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