topic Re: select max from character variables between 2 tables in SAS Programming

select max from character variables between 2 tables

teddyee — Sat, 24 Jun 2017 17:33:28 GMT

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

s8889b 1/2/2017 Y
S9990n 2/2/2017 N
T6789p 3/2/2017 Y
Z1234 4/2/2017

i am not able to get the filter column in the desired outcome, where Y>N>A>null (by alphabetical order), with my code below....

proc sql;
create table final as
select a.customer,
max(a.filter, b.filter) as filter_new -------------------------------> how should i change this line to achieve my desired outcome?
case when coalesce(a.date,b.date) is not null
then max(a.date,b.date)
end as date_new format date9.
from work.cards a
left join work.cp b on a.customer=b.customer;
quit;

Re: select max from character variables between 2 tables

Reeza — Sat, 24 Jun 2017 17:52:50 GMT

Have you tried an UPDATE statement?

Re: select max from character variables between 2 tables

Tom — Sat, 24 Jun 2017 18:18:49 GMT

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;

Re: select max from character variables between 2 tables

Patrick — Sun, 25 Jun 2017 01:05:32 GMT

@teddyee

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.