topic Re: How to apply customer information in one set to a set of transactions in another using Key ID in SAS Programming

How to apply customer information in one set to a set of transactions in another using Key ID

smcconn5 — Tue, 24 Sep 2019 15:07:50 GMT

Hello!

I have 2 sets of data in SAS Studio. The 1st data set has basic customer information (customer ID, city, state, etc...) and the 2nd data set has a listing of transaction by each customer. I need to append the customer data to the transaction data using the customer ID. I've tried using the merge function, but it then narrows down the transaction information down to 1 observation.

data transactions;
      set CD8940.transaction_data;
run;

data customer_info;
     set CD8940.customer_info;
run;

data merged;
	merge transactions customer_info;
run;

Any help is greatly appreciated!

Re: How to apply customer information in one set to a set of transactions in another using Key ID

ballardw — Tue, 24 Sep 2019 15:21:24 GMT

You did not provide any thing to tell the Merge to use the ID value. Which would be a BY ID statement if both sets are sorted by Id.

However Merge may run into issues with many-to-one (multiple transactions for one customer) and the order you place the data sets on a merge statement.

Proc SQL has many tools for combining data in interesting ways. One way:

proc sql;
   create table joined as
   select a.*, b.city, b.state
   from cd8940.transactiondata as a
        left join
        cd8940.customer_info as b
        on a.id = b.id
   ;
quit;

Re: How to apply customer information in one set to a set of transactions in another using Key ID

smcconn5 — Tue, 24 Sep 2019 16:39:13 GMT

Worked like a charm!

Thank you!