BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Muyu
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

2 REPLIES 2
Muyu
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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