table a
customer date filter
s8889b 1/2/2017 N
S9990n 2/2/2017 A
T6789p Y
Z1234 4/2/2017
table b
customer date filter
s8889b 1/1/2017 Y
S9990n 2/2/2017 N
T6789p 3/2/2017 N
Z1234
desired outcome
customer date filter
Below code creates your desired outcome.
data table_a;
infile datalines truncover dlm=' ' dsd;
input customer $ date:anydtdte. filter $;
format date date9.;
datalines;
s8889b 1/2/2017 N
S9990n 2/2/2017 A
T6789p Y
Z1234 4/2/2017
;
run;
data table_b;
infile datalines truncover dlm=' ' dsd;
input customer $ date:anydtdte. filter $;
format date date9.;
datalines;
s8889b 1/1/2017 Y
S9990n 2/2/2017 N
T6789p 3/2/2017 N
Z1234
;
run;
proc sql;
create table want as
select
coalesce(a.customer,b.customer) as customer,
max(a.date,b.date) as date format=date9.,
byte(max(rank(a.filter),rank(b.filter))) as filter
from
table_a as a
full outer join
table_b as b
on
a.customer=b.customer
;
quit;
Please post in the future fully working data steps creating your sample data so we don't have to do this for you.
Have you tried an UPDATE statement?
Please explain what it is you want to happen. First just use words and then use concrete examples of specific cases.
For example in your posted data there is a CUSTOMER 's8889b' that in A has DATE='02JAN2017'd and FILTER='N" and in B has DATE='01JAN2017'd and FILTER='Y'. But in the output you have taken the DATE from A and FILTER from B. Why?
Did you want to have the latest date and the maximum filter?
As to your code make sure that you are clear on whether you want to use the MAX() aggregate function of SQL (which works across observations) or the SAS function MAX(,) which takes the maximum of a list of two or more individual values.
If you just want to stack up the data and take the max per customer then use a sub-query to stack (union) the data.
proc sql noprint ;
create table want as
select customer
, max(date) as date format=yymmdd10.
, max(filter) as filter
from (
select * from A
union
select * from B
)
group by 1
;
quit;
Below code creates your desired outcome.
data table_a;
infile datalines truncover dlm=' ' dsd;
input customer $ date:anydtdte. filter $;
format date date9.;
datalines;
s8889b 1/2/2017 N
S9990n 2/2/2017 A
T6789p Y
Z1234 4/2/2017
;
run;
data table_b;
infile datalines truncover dlm=' ' dsd;
input customer $ date:anydtdte. filter $;
format date date9.;
datalines;
s8889b 1/1/2017 Y
S9990n 2/2/2017 N
T6789p 3/2/2017 N
Z1234
;
run;
proc sql;
create table want as
select
coalesce(a.customer,b.customer) as customer,
max(a.date,b.date) as date format=date9.,
byte(max(rank(a.filter),rank(b.filter))) as filter
from
table_a as a
full outer join
table_b as b
on
a.customer=b.customer
;
quit;
Please post in the future fully working data steps creating your sample data so we don't have to do this for you.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.