BookmarkSubscribeRSS Feed
MarcTC
Obsidian | Level 7

Suppose a customer can have multiple customer accounts and purchase_summary table summarizes the purchase totals at customer account level. Not every customer account would have purchase transactions. I use the following query to create a table which includes only customers which had purchase transactions. This query needs to use purchase_summary table twice. I wonder if it is possible to further simplify this query to use purchase_summary once only.

proc sql;

create table a1 as

select a.*, b.purchase_total

from customer a left join purchase_summary b

on a.customer_id = b.customer_id

and a.customer_account = b.customer_account

where a.customer_id in (select customer_id from purchase_summary)

; quit;

3 REPLIES 3
art297
Opal | Level 21

If I correctly understand what you want, I don't think your code is doing it.  I tried it with the following test files.

Conversely, I think a simple full join does do what you want.  Take a look at the following:

/* Some test data */

data customer;

  input customer_id customer_account ina;

  cards;

1 1 1

1 2 1

2 1 1

3 1 1

4 1 1

;

data purchase_summary;

  input customer_id customer_account purchase_total;

  cards;

1 1 10

3 1 20

4 1 30

5 1 40

;

/*what you did */

proc sql;

create table a1 as

select a.*, b.purchase_total

from customer a left join purchase_summary b

on a.customer_id = b.customer_id

and a.customer_account = b.customer_account

where a.customer_id in (select customer_id from purchase_summary)

; quit;

/*what I think you want to do*/

proc sql;

  create table a2 as

    select a.*, b.purchase_total

      from customer a, purchase_summary b

        having a.customer_id = b.customer_id

        and a.customer_account = b.customer_account

;

quit;

FriedEgg
SAS Employee

Pretty sure I am not following the concept of your data but if you provided a better outline of the intended input and output I and others could probably be more helpful.

data purchase_summary;

input customer_account purchase_summary;

cards;

123 100.00

124 100.50

125  99.99

126   1.00

127 999.00

;

run;

data customer;

input customer_id customer_account;

cards;

1 123

1 124

2 125

2 126

3 127

;

run;

proc sql;

create table total as

select a.customer_id, sum(purchase_summary) as purchase_total

   from customer a, purchase_summary b

  where a.customer_account=b.customer_account

  group by customer_id;

quit;

Tom
Super User Tom
Super User

If you only want the ones that are in both datasets then do not use a LEFT JOIN.

proc sql;

  create table a1 as

   select a.*, b.purchase_total

   from customer a 

        , purchase_summary b

   where a.customer_id = b.customer_id

      and a.customer_account = b.customer_account

  ;

quit;

Or translated into normal SAS programming:

data a1;

   merge customer (in=in1) purchase_total(in=in2 keep=customer_id customer_account purchase_total) ;

   by customer_id customer_account ;

   if in1 and in2;

run;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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
  • 1303 views
  • 0 likes
  • 4 in conversation