topic Removing duplicate records based on certain variables in SAS Programming

Removing duplicate records based on certain variables

Ranjeeta — Wed, 30 Oct 2019 20:09:19 GMT

PROC CONTENTS of the COMBINEDSORTED DATASET:

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat4521367

ADM_DT	Num	8	DATETIME20.	DATETIME20.
ADM_TM	Num	8	DATETIME20.	DATETIME20.
CIHI_KEY	Char	18		$16.
HCNE	Char	10		$10.
INST_NO	Char	4		$4.
REG_DT	Num	8	DATETIME20.	ANYDTDTM40.
REG_TM	Num	8	DATETIME20.	ANYDTDTM40

I am not sure why the temp is returning blank values

I am basically trying to delete any duplicate records that exist for HCNE by keeping the record with the earlier REG_TM or ADM_TM

I am not sure if the formatting is cusing the issue

Please advise how to correct the same

Regards



data two(keep=HCNE REG_TM ADM_TM  temp);                                                                                                     
  set Combinedsorted end=finished;                                                                                                                 
   by HCNE;                                                                                                                               
  retain temp;                                                                                                                          
  if not finished then do;                                                                                                              
  pt = _N_ + 1;                                                                                                                         
  set Combinedsorted(rename= (HCNE=next_id  REG_TM=next_regtm ADM_TM=next_admtm)) point=pt;                                                            
                                                                                                                                        
  if first.HCNE then do;                                                                                                                  
   if REG_TM < ADM_TM then temp=REG_TM;                                                                                                    
    else temp=ADM_TM;                                                                                                                    
                                                                                                                                        
     if next_regtm < temp then temp=next_regtm;                                                                                         
      else if next_admtm < temp then temp=next_admtm;                                                                                   
  end;                                                                                                                                  
                                                                                                                                        
  end;                                                                                                                                  
run;                                                                                                                                    
                                                                                                                                        
data final;                                                                                                                             
 set two;                                                                                                                               
   if REG_TM=temp or ADM_TM=temp then output;                                                                                             
run;                                                                                                                                    
                                                                                                                                        
proc print;                                                                                                                             
 format REG_TM ADM_TM temp DATETIME20.;                                                                                                         
run;

Re: Removing duplicate records based on certain variables

hashman — Wed, 30 Oct 2019 20:42:31 GMT

@Ranjeeta: Please show a small sample of your input data and the corresponding sample of output data you want to generate. A picture of data is worth a thousand words about the data.

Kind regards

Paul D.

Re: Removing duplicate records based on certain variables

ChrisNZ — Wed, 30 Oct 2019 21:12:24 GMT

Something like this? (untested as you don't supply a data step with test data)


data WANT;
  merge COMBINEDSORTED
        COMBINEDSORTED(firstobs=2 rename=(HCNE=NEXT_ID  REG_TM=NEXT_REGTM ADM_TM=NEXT_ADMTM)) ;
  if HCNE=NEXT_ID ;
  MIN=min(REG_TM, NEXT_REGTM, ADM_TM, NEXT_ADMTM );
  if MIN=NEXT_REGTM | MIN=NEXT_ADMTM then do;
    REG_TM=NEXT_REGTM;
    ADM_TM=NEXT_ADMTM;
  end; 
run;