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;
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
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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.