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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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