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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1325 views
  • 0 likes
  • 2 in conversation