Hash Object Key Summary Variable

[taishayla]

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 . .

[s_lassen · Accepted solution]

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;            

[taishayla]

This is exactly what I needed. Thanks for sharing your expertise!