topic Re: Data Manipulate in SAS Programming

Data Manipulate

sijansap — Thu, 12 Oct 2017 01:16:56 GMT

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

Re: Data Manipulate

Reeza — Thu, 12 Oct 2017 03:17:29 GMT

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.

Re: Data Manipulate

sijansap — Thu, 12 Oct 2017 03:45:54 GMT

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

Re: Data Manipulate

Reeza — Thu, 12 Oct 2017 03:48:08 GMT

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

Re: Data Manipulate

PSNn — Thu, 12 Oct 2017 08:02:34 GMT

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;

Re: Data Manipulate

s_lassen — Thu, 12 Oct 2017 11:00:34 GMT

@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;

Re: Data Manipulate

SuzanneDorinski — Thu, 12 Oct 2017 11:56:07 GMT

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;

Re: Data Manipulate

sijansap — Thu, 12 Oct 2017 16:16:24 GMT

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