BookmarkSubscribeRSS Feed
Archibald2
Calcite | Level 5

Hello, 

I need to merge the DATASET 1  with DATASET 2  to get to a final dataset "DATA WANT" and I need some guidance.

please note that dataset 1 is a matched pair data of students and dataset 2  contains additional variables on the matched pairs data.

 

DATASET 1

groupA_idgroupB_idgroupA_gradegroupB_gradegroupA_sexgroupB_sex
00000-00000100000-0002002211
00000-00000100000-0002012211
00000-00000100000-0002022211
00000-00000200000-0002032211
00000-00000200000-0002042211
00000-00000200000-0002052211
00000-00000300000-0002062211
00000-00000300000-0002072211
00000-00000300000-0002082211
 

 
DATASET 2
 
idSiblingseconomic status
00000-000001Yeslow
00000-000001yeslow
00000-000001yeslow
00000-000002yeslow
00000-000002yeslow
00000-000002yeslow
00000-000003nohigh
00000-000003nohigh
00000-000003nohigh
00000-000200yeslow
00000-000201nolow
00000-000202nohigh
00000-000203nomedium
00000-000204nohigh
00000-000205yeslow
00000-000206nomedium
00000-000207yeshigh
00000-000208yeshigh
 

DATA WANT

 

idgradesexSiblingseconomic status
00000-00000121Yeslow
00000-00000221yeslow
00000-00000321nohigh
00000-00000221yeslow
00000-00020021yeslow
00000-00020121nolow
00000-00020221nohigh
00000-00020321nomedium
00000-00020421nohigh
00000-00020521yeslow
00000-00020621nomedium
00000-00020721yeshigh
00000-00020821yeshigh

 

Any help would be appreciated. Thanks
2 REPLIES 2
hashman
Ammonite | Level 13

Your sample output is inconsistent in terms of keeping ID duplicates because ID=00000-000002 is duplicated while the rest are not. Assuming that it's a typo and you don't want any dupes but only the first ID encountered in the data set 2, you can proceed as follows:

 

data want (drop = group: rc) ;                                                 
  if _n_ = 1 then do ;                                                         
    dcl hash h (ordered:"a") ;                                                 
    h.defineKey ("id") ;                                                       
    h.defineData ("id", "grade", "sex") ;                                      
    h.defineDone () ;                                                          
    do until (z) ;                                                             
      set ds1 end = z ;                                                        
      id = groupa_id ; grade = groupa_grade ; sex = groupa_sex ; rc = h.add() ;
      id = groupb_id ; grade = groupb_grade ; sex = groupb_sex ; rc = h.add() ;
    end ;                                                                      
    h.output (dataset:"hash") ;                                                
    dcl hash u () ;                                                            
    u.defineKey ("id") ;                                                       
    u.defineDone () ;                                                          
  end ;                                                                        
  set ds2 ;                                           
  if u.check() ne 0 ;                                                          
  u.add() ;                                                                    
  if h.find() ne 0 then call missing (grade, sex) ;                            
run ;                                                                          

If you want to keep the dupes from DS2, get rid of the hash table U and the related statements with the method calls referencing U.

Archibald2
Calcite | Level 5

Many thanks hashman

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 863 views
  • 0 likes
  • 2 in conversation