topic Lookup SAS in New SAS User

Lookup SAS

sameer112217 — Thu, 27 Jun 2019 20:27:01 GMT

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.

Re: Lookup SAS

Ksharp — Fri, 28 Jun 2019 13:57:31 GMT

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;

Re: Lookup SAS

sameer112217 — Sat, 29 Jun 2019 08:43:51 GMT

thanks ksharp... thats wonderful ..it works