topic how to look up value for a newly created column from another table in SAS Programming

how to look up value for a newly created column from another table

heretolearnSAS — Fri, 17 Jun 2022 08:51:49 GMT

Hi all, I am a beginner in SAS, so please bear with my question. Basically, I want to look up the value for my dataset from another dataset, but not sure how to do it in a faster way (as the data is millions). I tried using proc sql but it took very long and failed.

Let's say I created a new column for my current dataset as below:

let's say this dataset called 'abc'

customer	date
112
114
203
158

Then, the lookup dataset called 'lookup' is as below:

customer	date
112	201002
114	201004
203	201006
158	201007
203	200901
158	200704

As you can observe from the lookup table above, there are duplicate customer numbers with different dates. However, what I wanna do is to look up the earliest date from the lookup table and append them into the 'abc' dataset.

The below shows the expected output I wish to have for abc table:

customer	date
112	201002
114	201004
203	200901
158	200704

Appreciate if anyone can help (this is million row dataset)

Re: how to look up value for a newly created column from another table

novinosrin — Fri, 17 Jun 2022 12:47:53 GMT


data have1;
 input customer	date;
 cards;
112 .	 
114	. 
203	. 
158	. 
;

data have2;
 input customer	date :yymmn6.;
 format date yymmn6.;
 cards;
112	201002
114	201004
203	201006
158	201007
203	200901
158	200704
;


proc sql;
  create table want as
  select a.customer, b.date
  from have1 a left join have2 b
  on a.customer = b.customer
  group by a.customer
  having min(b.date) = b.date;
quit;

Re: how to look up value for a newly created column from another table

PeterClemmensen — Fri, 17 Jun 2022 13:00:20 GMT

data current;
input customer_number date : yymmn6.;
datalines;
112 . 
114 . 
203 . 
158 . 
;

data lookup;
input customer_number date : yymmn6.;
format date yymmn6.;
datalines;
112 201002 
114 201004 
203 201006 
158 201007 
203 200901 
158 200704 
;

proc sql;
   create table want as
   select c.customer_number
        , min_date format = yymmn6.
   from current c
   left join 
         (select customer_number
               , min(date) as min_date
          from lookup
          group by customer_number
         ) l
   on c.customer_number = l.customer_number
   ;
quit;