topic Re: How to convert Customer IDs to Customer Base Key? in SAS Programming

How to convert Customer IDs to Customer Base Key?

Muyu — Fri, 17 Mar 2017 15:25:25 GMT

Hi there,

I have an excel file which contains customer IDs, and I want to convert customer IDs to custoemr base key.

My manager told me I could do it in SAS, and she gave me a sample. However, I do not know how may I apply the SAS code to Excel like where and how should I input and edit the excel file.

The following is the code.

rsubmit;

proc sql;

connect to db2(database = owstar);

CREATE table test as

SELECT *

FROM connection to DB2 (

select a.cust_base_key,

/* a.acct_base_key,*/

a.prim_cust_f,

b.cust_id

from owstar.iwf_cust_acct a join OWTACT.cust_xref b

on a.CUST_BASE_KEY=b.CUST_BASE_KEY

where a.time_key in (16636) /* Oct 2016 */

ORDER by

cust_id

);

QUIT;

run;

rsubmit;

data simon2;

set simon;

pst = put(cust_id, $20.) ;

drop cust_id;

rename pst =cust_id;

run;

proc sort data = simon2;

by cust_id;

run;

data test2 (drop=prim_cust_f);

merge simon2(in=a) test(in=b);

by cust_id;

if a;

run;

proc sort data =test2 out=test3 nodupkey;

by cust_id cust_base_key transit;

run;

Thank you

Re: How to convert Customer IDs to Customer Base Key?

Muyu — Fri, 17 Mar 2017 17:47:50 GMT

Is my code wrong? Why does no one reply me ...

Re: How to convert Customer IDs to Customer Base Key?

Kurt_Bremser — Fri, 17 Mar 2017 18:00:06 GMT

The code as such doesn't make sense.

- there are no endrsubmit's for the rsubmit's, so it is unclear what is executed where

- a dataset test is created, but never used

- a dataset simon is used, but where does it come from?

I can guess that you get a lookup table from DB/2 that contains keys and id's.

Your first step will be to import the data from the Excel file into SAS. Then you have to make sure that the customer id`s from Excel can be matched to those from DB/2 (same variable attributes, same contents). Then you can join the two tables on customer id.