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)
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.