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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.