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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 646 views
  • 0 likes
  • 2 in conversation