BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
teddyee
Fluorite | Level 6

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;
 
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

3 REPLIES 3
Reeza
Super User

Have you tried an UPDATE statement?

Tom
Super User Tom
Super User

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;

 

Patrick
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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