Lookup SAS

[sameer112217]

Hello Everyone

Need one help

Old_Customer_id      Customer_id        Updated_ID (This is the column I need)

301                               100                     301

.                                    100                     301

                                     101                     101

305                               102                      305        

310                                102                     305

                                      103                    103

                                      104                    104

So I need the above results in the updated_id variable based on three conditions.

Condition 1 If customer_id has 101 value, then I need 301 in the updated_id variable in both rows . Zero should be ignored

Condition 2 If 102 customer_id has two values against it 305 and 310 in old_customer_id, then I need 305 in the updated_id as it is minimum in both rows

Condition 3: This is simple. If there are no values against that particular customer_id in the old_customer_id then I need customer_id value in the updated_id which is the case with 103 and 104.

I wrote a code using SQL lookup and removing zero,but it did not convince my client. I needs other approach because the data size is in millions.

If

[Ksharp · Accepted solution]

If I understood your question.

data have;
input Old_Customer_id      Customer_id;
cards;
301                               100               
.                                    100                 
.                                     101                     
305                               102                      
310                                102                     
.                                      103                   
 .                                     104  
;
proc sort data=have out=temp;
by  Customer_id   ;
run;
data want;
 do until(last.Customer_id);
   set temp;
   by  Customer_id;
   want=min(want,Old_Customer_id)  ;
 end;
  do until(last.Customer_id);
   set temp;
   by  Customer_id;
   want=coalesce(want,Customer_id);
   output;
 end;
run;

[sameer112217]

thanks ksharp... thats wonderful ..it works