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
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.
Is my code wrong? Why does no one reply me ...
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.
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.
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.