Hi all, I have a dataset which has date_of_birth of the customer. As seen in the data, few icustomerid are same and they have a different date_of_birth. I want the output data to be considering the odd date_of_birth as bad dates. For e.g., For icustomerid 479864, two DOB is 01DEC67 and 1 is 02DEC68. So I want to mark the 02DEC68 as bad date as it is not meeting the consistency with other DOBs. Also icustomerid 374534 has one odd DOB i.e., 12SEP56 so that should be considered as bad date. I want to identify which dates of birth (DOB) appear the most in a list, and mark any DOBs that appear fewer times than the most common one with a 1. For example, if the list contains the following DOBs: 1/1/2000 2/2/2000 1/1/2000 The output should be: 1/1/2000 0 2/2/2000 1. Thanks Sample Dataset Data Bad_Dates; infile cards expandtabs; input icustomerid debt_code dr_inits $ dr_name $ dr_address1 $ dr_address2 $ dr_postcode $ Date_of_birth $ Num_of_accs ; datalines ; 355774 316997113 Richard Williams 69MainwaringDrive SaltneyFerry CH40AZ 16JUN52 2 355774 320478910 Richard Williams 69MainwaringDrive SaltneyFerry CH40AZ 16JUN52 2 375818 379299084 Lynn Summerhayes 6Crossways Jaywick CO152NB 12MAR63 1 479864 328107651 Garry Wyatt 79 CadewellLane TQ27HP 01DEC67 3 479864 261721781 Garry Wyett 79 CadewellLane TQ27HP 02DEC68 3 479864 328908108 Garry Wyatt 79 CadewellLane TQ27HP 01DEC67 3 374534 157389842 Sandra Motram 12PughRoad Bradley WV148PT 09SEP56 4 374534 157389826 Sandra Motram 12PughRoad Bradley WV148PT 09SEP56 4 374534 157389800 Sandra Motram 12PughRoad Bradley WV148PT 09SEP56 4 374534 211701685 Sandra Motram 12PughRoad Bradley WV148PT 12SEP56 4 ; run; DATA bad_dates; SET Linked_acc; IF icustomerid=lag(icustomerid) and date_of_birth ne lag(date_of_birth) THEN bad_date=1; ELSE bad_date=0; RUN; ... View more

Hi Experts, I have a dataset which has repeated reference_number. I do not want to delete anything but just want to add a new column which can show 1 if the reference_number is repeaded or else 0. Can you please suggest how can I do that. I have used the below code. This highlight the first time reference_number as 0 and the repeated reference_number as 1. I want if the reference_number is not repeated then 0 and if repeated then 1. data repeated_accounts; set Equifax_files; if lag(reference_number)=reference_number then repeat_flag=1; else repeat_flag=0; run; ... View more

Hi all, I am trying to merge three datasets. I have tried two different ways and they both are giving me incorrect numbers (because each file has around 1 million records, so total should be around 3 millions). One of the way is removing duplicate reference_number and giving me 1.7 million records and other is giving me 4.3 million records. Can you please suggest the correct way to merge these datasets? /*Merge multiple datasets with reference_number as common value*/ proc sql; create table Equifax_files as select * from work.elnz_lowell_11oct union all select * from work.elnz_lowell_10feb union all select * from work.elnz_lowell_12sep order by reference_number; quit; /* Method 2 */ Data Equifax_files ; merge work.elnz_lowell_12sep work.elnz_lowell_11oct work.elnz_lowell_10feb; by reference_number; run; ... View more