topic Re: Sales and Payment Data Merging in SAS Enterprise Guide

Sales and Payment Data Merging

_el_doredo — Fri, 01 Jul 2022 10:51:54 GMT

Hello Experts.

I want to join/merge sales and payment data. But, my summarization data is giving me duplicate sales amount. I dont want that

Datasets are below:

data payment;
infile cards dlm=',' dsd;
input ticketid :$20. Promo_Type Payment_Type :$12.
customer_id :$15. OrderDate :yymmdd10. Promo_Des :$30. Payment_Amt;
format OrderDate yymmdd10.;
cards;
A098765,,credit,C014532,2021-05-11,,500
A098765,,cash,C014532,2021-05-11,,100
A098765,,cash,C014532,2021-05-11,,180
A098765,,check,C014532,2021-05-11,,200
A098765,8360,Finance,C014532,2021-05-11,"36 months",5000
A098765,8600,Finance,C014532,2021-05-11,"60 months",3500
;
run;

data Sales;
infile cards dlm=',' dsd;
input ticketid :$20. ProductId :$20. customer_id :$15. OrderDate :yymmdd10. Sales_amt;
format OrderDate yymmdd10.;
cards;
A098765,"AX-14589",C014532,2021-05-11,1570
;
run;

I want my output like this

Assume Two filters are there.

1.Payment_Type

2.Promo_Type

Orderdate	sum of sales_amt	sum of payment_amt
11-05-2021	1570	9480

If i select select Payment_type as Cash means my output need to be

Orderdate	sum of sales_amt	sum of payment_amt
11-05-2021	0	280

If i select Promo_Type as 8360 means my output need to be

Orderdate	sum of sales_amt	sum of payment_amt
11-05-2021	0	5000

Can some one help me on this?

Thanks in Advance

Re: Sales and Payment Data Merging

PaigeMiller — Fri, 01 Jul 2022 11:21:24 GMT

@_el_doredo wrote:

I want to join/merge sales and payment data.

Join/merge on what variables? You don't tell us, but we would need to know.

But, my summarization data is giving me duplicate sales amount. I dont want that

Show us your code.

Re: Sales and Payment Data Merging

_el_doredo — Fri, 01 Jul 2022 11:56:17 GMT

@PaigeMiller

This is my code

Proc sql;
create table final as
select b.orderdate,
b.promo_type,
b.payment_type,
sum(b.Payment_Amt) as Payment_amt,
sum(a.Sales_Amt) as Sales_amt
from sales a
left join payment b
on a.orderdate=b.orderdate
group by 1,2,3
;
quit;

Re: Sales and Payment Data Merging

PaigeMiller — Fri, 01 Jul 2022 13:20:34 GMT

Simple debugging of your code (which you should do yourself from now on): I modify your PROC SQL so we can see what the merge is doing

proc sql;
    create table final as
    select b.orderdate,
        b.promo_type,
        b.payment_type,
        b.payment_amt,
        a.sales_amt
    from sales a
    left join payment b on a.orderdate=b.orderdate ;
quit;

This produces the following table:

So when Payment_type is cash, there are two records and the sum of the sales amount would then be doubled

Which brings up the question ... why do you want to do this merge in the first place? Why don't you compute total sales from the sales data and total payments from the payment data?

Re: Sales and Payment Data Merging

_el_doredo — Fri, 15 Jul 2022 08:16:46 GMT

Sorry for the last response. Actually requirement is not right in this case..

Thanks for your help