topic Re: use HASH to realize the logic in SAS Programming

use HASH to realize the logic

summy — Mon, 07 May 2012 12:02:26 GMT

Hi.

I have a dataset with two variables,one is ID which is not unique and the other is Scores.I want to create a new variable New_Score.

Here is the logic:

1)If the ID have the value of 12 or 18,then New_Score equal to 12 or 18;

2)If the ID have the value of 12 and also 18,then New_Score equal to 12;

3)If all the values of ID are less than 100,then New_Score equal to minimun value of ID;

4)If not all the values of ID are less than 100,then New_Score equal to minimun value of ID that are greater than or equal to 100;

For example:

data m01;

input ID Scores;

cards;

1 12

1 20

2 18

2 99

3 12

3 18

4 27

4 99

5 80

5 100

5 180

;

run;

The dataset I want is this:

1 12

2 18

3 12

4 27

5 100

I want to use HASH to create the dataset,but I do not how to do it.Please help me.Thanks in advance.

Re: use HASH to realize the logic

FriedEgg — Mon, 07 May 2012 19:01:20 GMT

data foo;

input id _score @@;

cards;

1 12 1 20 2 18 2 99 3 12 3 18

4 27 4 99 5 80 5 100 5 180

;

run;

/* with hash */

data bar;

if 0 then set foo;

dcl hash foo(dataset:'foo',multidata:'y');

foo.definekey('id');

foo.definedata(all:'y');

foo.definedone();

dcl hash k(dataset:'foo',ordered:'y',duplicate:'r');

k.definekey('id');

k.definedata('id');

k.definedone();

dcl hiter iter('k');

rc=iter.first();

do while(rc=0);

rc=foo.find();

score=_score;

r=foo.find_next();

do while(r=0);

select;

when (_score ge 100 and score ge 100) score=min(score,_score);

when (_score ge 100 and score lt 100) score=max(score,_score);

when (_score eq 12 or score eq 12) score=12;

when (_score eq 18 or score eq 18) score=18;

otherwise score=min(score,_score);

end;

r=foo.find_next();

end;

output;

rc=iter.next();

end;

keep id score;

stop;

run;

/* with dow */

proc sort data=foo; by id _score; run;

data bar;

do until(last.id);

set foo;

by id;

select;

when(_score ge 100 and score ge 100) score=min(score,_score);

when(_score ge 100 and score lt 100) score=max(score,_score);

when(_score eq 12 or score eq 12) score=12;

when(_score eq 18 or score eq 18) score=18;

otherwise score=min(score,_score);

end;

output;

drop _:;

run;

Re: use HASH to realize the logic

chang_y_chung_hotmail_com — Mon, 07 May 2012 19:52:27 GMT

Well, here is another way. No hash, though.

/* sample data */
data one;
   infile cards missover;
   input id score @;
   do while (not missing(score));
      output;
      input score @;
   end;
cards;
1 12 20
2 18 99
3 12 18
4 27 99
5 80 100 180
;
run;

/* aggregate */
data two;
   has12 = 0; has18 = 0; hasGE100 = 0;
   min1 = 99999; min2 = 99999; /* larger than any possible score */

   do until (last.id);
      set one;
      by id;

      select;
      when(score = 12) has12 = 1;
      when(score = 18) has18 = 1;
      when(100 <= score) hasGE100 = 1;
      otherwise;
      end;

      min1 = min(score, min1);
      if 100<=score then min2 = min(score, min2);
   end;

   select;
   when (has12 and has18) new_score = 12;
   when (has12) new_score = 12;
   when (has18) new_score = 18;
   when (hasGE100) new_score = min2;
   otherwise new_score = min1;
   end;

   keep id new_score;
run;

/* check */
proc print data=two noobs;
run;
/* on lst
       new_
id    score

1      12
2      18
3      12
4      27
5     100
*/

Re: use HASH to realize the logic

summy — Tue, 08 May 2012 14:42:21 GMT

Thanks!But the hash code can not run at SAS 9.1.3.

Re: use HASH to realize the logic

FriedEgg — Tue, 08 May 2012 15:16:33 GMT

Summy,

Hash objects in 9.1.3 cannot hold multiple data records per key. While there are still ways to do this on your version with hash, I would recommend taking one of the DOW style approaches myself and chang_y_chung demonstrated.

Re: use HASH to realize the logic

summy — Wed, 09 May 2012 14:13:25 GMT

FriedEgg,

Thanks! I learn from yours and chang_y_chung's.]And find the way.

data m01;
input id score @@;
cards;
1 12 1 4 1 20 1 109 2 189 2 18 2 99 3 12 3 18 3 146 4 27 4 99 5 80 5 100 5 180 6 101 6 700 7 12 8 99
;
run;
data _NULL_;
length has12 has18 hasge100 min1 min2 new_score 8.;
if _N_=1 then do;
declare hash h(ordered:"a");
     h.definekey("id");
     h.definedata("id","has12","has18","hasge100","min1","min2","new_score");
     h.definedone();
    end;
set m01 end=last;
if h.find() ne 0 then do;
has12=0;has18=0;hasge100=0;
min1=9999;min2=9999;
new_score=0;
h.add();
end;
if h.find()=0 then Do;
if score=12 then has12=1;
if score=18 then has18=1;
if score ge 100 then hasge100=1;
/* h.replace();*/
max=max(score,max);
min1=min(score,min1);
if hasge100=1 then min2=min(score,min2);
new_score=min1;
if hasge100=1 then new_score=min2;
if has18=1 then new_score=18;
if has12=1 then new_score=12;
if has12=1 and has18=1 then new_score=12;
/* if has12=1 then new_score=12;*/
/* if has18=1 then new_score=18;*/
/* if has12=1 and has18=1 then new_score=12;*/
/* if hasge100=1 then new_score=min2;*/
/* else new_score=min1;*/
h.replace();
end;
if last then h.output(dataset:"m02");
run;
proc print data=m02(keep=id new_score) noobs;run;