Hello - I am trying to use a hash object to create a key summary variable (totalsurveys). My goal is to get the total number of surveys completed for each client (see below table of what I'd like to achieve). My SAS code is as follows, but is not giving what I am looking for (help is appreciated!):
Data example; set example;
by participantid assessmentid datapointid;
*count=1;
if first.assessmentid and datapointid=1 then do;
count=1;
declare hash myhash(multidata: 'y',suminc: 'count');
myhash.definekey('participantid','first.assessmentid');
myhash.definedone();
myhash.add();
myhash.find();
myhash.sum(sum: totalsurveys);
end;
run;
Goal:
ParticipantID AssessmentID DataPointID count totalsurveys
1 46974 572 1 1
1 46974 573 . .
1 46974 574 . .
1 574487 572 1 2
1 574487 573 . .
1 574487 574 . .
2 695197 572 1 1
2 695197 573 . .
2 695197 574 . .
3 695197 572 1 1
3 695197 573 . .
3 695197 574 . .
4 695197 572 1 1
4 1762071 573
4 1762071 574 . .
4 1762071 572 1 2
4 1762071 573 . .
4 1762071 574 . .
Not quite sure what you are trying to accomplish with the hash table.
If your input data looks like this (the original data was not sorted as assumed in the program)
data have;
input
ParticipantID AssessmentID DataPointID;
cards;
1 46974 572
1 46974 573
1 46974 574
1 574487 572
1 574487 573
1 574487 574
2 695197 572
2 695197 573
2 695197 574
3 695197 572
3 695197 573
3 695197 574
4 695197 572
4 695197 573
4 695197 574
4 1762071 572
4 1762071 573
4 1762071 574
;run;
I think you can get the result you want like this:
data first;
set have;
by participantid assessmentid DataPointID;
if first.assessmentid;
retain count 1;
if first.participantid then
totalsurveys=1;
else
totalsurveys+1;
run;
data want;
merge have first;
by participantid assessmentid datapointid;
run;
Not quite sure what you are trying to accomplish with the hash table.
If your input data looks like this (the original data was not sorted as assumed in the program)
data have;
input
ParticipantID AssessmentID DataPointID;
cards;
1 46974 572
1 46974 573
1 46974 574
1 574487 572
1 574487 573
1 574487 574
2 695197 572
2 695197 573
2 695197 574
3 695197 572
3 695197 573
3 695197 574
4 695197 572
4 695197 573
4 695197 574
4 1762071 572
4 1762071 573
4 1762071 574
;run;
I think you can get the result you want like this:
data first;
set have;
by participantid assessmentid DataPointID;
if first.assessmentid;
retain count 1;
if first.participantid then
totalsurveys=1;
else
totalsurveys+1;
run;
data want;
merge have first;
by participantid assessmentid datapointid;
run;
This is exactly what I needed. Thanks for sharing your expertise!
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: