Using hash to select last row by group

Ronein — Thu, 07 Sep 2023 05:35:26 GMT

Hello

Here is Hash code to select Last Row by group.

I want to ask-

Lets say there there is another data set called List_tbl and I want to select last row by group only for the groups in data set List_Tbl (So in this example I want to perform it only for groups 'A','C','D')

What is the way to tell SAS to perform it only for the groups in list_tbl?

Please note that in real life List_Tbl will have 200,000 groups and have table will have 5 millions groups.



data have;
input ID $ date : date9. var;
format date date9.;
cards;
A 01JAN2023 10
B 05JAN2023 5
B 06JAN2023 6
C 15JAN2023 7
C 16JAN2023 8
C 19JAN2023 9
C 20JAN2023 10
D 24JAN2023 2
;
Run;

Data List_tbl;
input ID $;
cards;
A
C
D
;
Run;

data _null_;
dcl hash H (dataset:'have',ordered: "a",duplicate:'r') ;
h.definekey  ("id") ;
h.definedata ("id","date","var") ;/***Var to show in wanted data set**/
h.definedone () ;
h.output(dataset:'want');
stop;
retain _n_   _iorc_ ;
if 0 then do;
set have;
output;
end;
run;

Re: Using hash to select last row by group

yabwon — Thu, 07 Sep 2023 05:49:58 GMT

How about that:

data have;
input ID $ date : date9. var;
format date date9.;
cards;
A 01JAN2023 10
B 05JAN2023 5
B 06JAN2023 6
C 15JAN2023 7
C 16JAN2023 8
C 19JAN2023 9
C 20JAN2023 10
D 24JAN2023 2
;
Run;

Data List_tbl;
input ID $;
cards;
A
C
D
;
Run;


/* sort have */
proc sort data = have;
  by ID date;
Run;

data WANT;

  /* use List_tbl as a dictionary */
  dcl hash H (dataset:'List_tbl');
  h.definekey  ("id") ;
  h.definedone () ;


  do until(END);
    set have end=END;
    by ID;
    /* check if it is the first row and is in the dictionary */
    if first.ID AND H.find()=0 then output;
  end;

  stop;
run;

proc print data = WANT;
run;

Bart

topic Using hash to select last row by group in SAS Programming

Using hash to select last row by group

Re: Using hash to select last row by group