select max from character variables between 2 tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

select max from character variables between 2 tables

[ Edited ]

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;
 

Accepted Solutions
Solution
‎06-24-2017 10:19 PM
Respected Advisor
Posts: 4,131

Re: select max from character variables between 2 tables

@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.

View solution in original post


All Replies
Super User
Posts: 19,052

Re: select max from character variables between 2 tables

Have you tried an UPDATE statement?

Super User
Super User
Posts: 6,842

Re: select max from character variables between 2 tables

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;

 

Solution
‎06-24-2017 10:19 PM
Respected Advisor
Posts: 4,131

Re: select max from character variables between 2 tables

@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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 146 views
  • 1 like
  • 4 in conversation