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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: