How to convert Customer IDs to Customer Base Key?

[Muyu]

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

[Muyu]

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

[Kurt_Bremser · Accepted solution]

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.