DATA Step, Macro, Functions and more

How to convert Customer IDs to Customer Base Key?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

How to convert Customer IDs to Customer Base Key?

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

 


Accepted Solutions
Solution
‎03-23-2017 09:52 AM
Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Occasional Contributor
Posts: 10

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

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

Solution
‎03-23-2017 09:52 AM
Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 108 views
  • 0 likes
  • 2 in conversation