Hi,
I have an excel file that has two columns in it, one is id and datetime stamp. I want to import this, read this data and combine with two other tables to retrieve a column for matching data.. I am able to create my lib, assign the sheet, use it. Also access my tables in the oracle library . Now, when I want to combine these two to get a output, I face an ERROR: BY variable ID is not on input data set WORK.TAB_RES
I am not sure what am i missing here, my tables are in Oracle database and my excel sheet is in my local mylib.
the column ID is in my excel sheet also in one of my joined tables. I want to retrieve Applications that has matching ID from the excel and from the tables.
sample values are
col A ColB
12345678 02-JAN-24 01.35.09.100000 PM
libname mylib XLSX 'filepath\filename.xlsx';
/*proc contents data=mylib._all_ nods;*/
/*run;*/
proc delete data=mydata;
DATA mydata;
SET mylib.Events; /* Replace 'sheet1' with the name of your Excel sheet */
/* by ID;*/
RUN;
rsubmit;
proc delete data =myora.c_ind;run;
proc sql;
connect to oracle(&ora_str.);
create table myora.c_ind as
select * from connection to oracle(
select *
/*channel_type, */
/*channel_ind, */
from table a inner join table b on
a.acct_id = b.acct_id
and a.arecd_dt >= '2023-03-01'
);
disconnect from oracle;
quit;
endrsubmit;
rsubmit;
proc delete data = tab_res;
run;
data tab_res;
set myora.c_ind;
by id;
run;
endrsubmit;
proc delete data=compare;
data compare;
merge tab_res (in=a) mydata (in=b);
by id;
if a and b;
run;
From now on, when you have an error in a PROC or DATA step, show us the log for that PROC or DATA step. We need to see the code as it appears in the log, and any messages such as errors, warnings and noes. DO NOT show us just the error messages in the log, detached from the code in the log.
Column ID is not in TAB_RES and so you cannot merge by ID.
First a general comment, separating an Error or warning message from the code is sub-optimal. Best is to include the entire log of the data step or proc that generated and error, include the code and all the messages. Copy from the log and paste into a code box on the forum.
In this case I strongly suggest that you run
Proc Contents data=work.tab_res;
run;
and show us all the results.
Considering that your code also shows building Tab_res I would suggest including the LOG for this step:
data tab_res;
set myora.c_ind;
by id;
run;
Since I do not see anything that attempts to ensure the order of BY variables in this process (i.e. Proc Sort by ID or SQL Order by ID ) I actually would expect this merge to fail at sometime unless your data sources are exceptionally clean about data order.
Which starting with Excel is problematic as you may get versions where your variable is character or numeric and so might have an issue with merging by that variable from a different data source that is always the same.
my full code here rsubmit; %let ora_str = user="&user." pass="&pwd." ; libname myora ORACLE &ora_str. DBSERVER_MAX_BYTES=1; %let sdate = &sysdate9.; endrsubmit; %let today = %sysfunc(putn(%eval(%sysfunc(today())),date9.)); %put &today; rsubmit; data _null_; call symput('dt',"'"||put("&sdate."d,yymmdd10.)||"'"); call symput('rpt',compress(put("&sdate."d,yymmdd10.),'-')); run; %let today = %sysfunc(putn(%eval(%sysfunc(today())),date9.)); %put &today; %let previous_day = %sysfunc(intnx(day, %sysfunc(today()), -1), date9.); %put &previous_day; %put &dt ; %put &rpt ; %sysrput dt=&dt ; %sysrput rpt=&rpt ; endrsubmit; libname mylib XLSX 'filepath\filename.xlsx'; proc contents data=mylib._all_ nods; run; /*proc delete data=mydata;*/ data app_evt; SET mylib.Events; /* Replace 'sheet1' with the name of your Excel sheet */ /* by CUSTID;*/ RUN; rsubmit; proc sql; connect to oracle(&ora_str. preserve_comments); create table c_ind as select * from connection to oracle( select a.*, b.* /*channel_type, */ /*channel_ind, */ from adv a , ak b where a.acct_id = b.acct_id and a.appl_dt >= '2023-03-01' ); disconnect from oracle; quit; endrsubmit; rsubmit; proc delete data = myora.tab_res; run; data myora.tab_res; set c_ind; run; endrsubmit; rsubmit; proc delete data=myora.app_ind; data myora.app_ind; set myora.tab_res; by custid; run; endrsubmit; /*rsubmit;*/ /*proc contents data=myora.app_ind;run;*/ /*endrsubmit;*/ rsubmit; data assign; set myora.app_ind; run; endrsubmit; rsubmit; proc contents data=assign;run; endrsubmit; proc print data=work.assign; var custid; run; proc print data=work.app_evt; var custid; run; data assign; set work.assign; custid = trim(custid); run; data app_evt; set work.app_evt; custid = trim(custid); run; rsubmit; data compare; merge work.assign (in=a) work.app_evt (in=b); by custid; if a and b; run; endrsubmit; proc contents data=mydata;run;
Error from the log, this is where it starts
40 /*rsubmit;*/ 41 /*proc contents data=myora.app_ind;run;*/ 42 /*endrsubmit;*/ 43 44 rsubmit; NOTE: Remote submit to ACXIOM commencing. 53 data assign; 54 set myora.app_ind; 55 run; NOTE: There were 468401 observations read from the data set MYORA.APP_IND. NOTE: The data set WORK.ASSIGN has 468401 observations and 181 variables. NOTE: Compressing data set WORK.ASSIGN decreased size by 45.76 percent. Compressed is 4537 pages; un-compressed would require 8365 pages. NOTE: DATA statement used (Total process time): real time 15.23 seconds cpu time 16.14 seconds NOTE: Remote submit to ACXIOM complete. 45 46 rsubmit; NOTE: Remote submit to ACXIOM commencing. 56 proc contents data=assign;run; NOTE: Non-portable document will be produced. The current settings of FORMCHAR use nonstandard line-drawing characters and the resulting output file will not render correctly unless all readers of the document have the SAS Monospace font installed. To make your document portable, issue the following command: OPTIONS FORMCHAR="|----|+|---+=|-/\<>*"; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.04 seconds cpu time 0.04 seconds NOTE: The PROCEDURE CONTENTS printed pages 1-5. NOTE: Remote submit to ACXIOM complete. 47 48 49 proc print data=work.assign; ERROR: File WORK.ASSIGN.DATA does not exist. 50 var custid; 51 run; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 52 53 proc print data=work.app_evt; 54 var custid; 55 run; NOTE: There were 79691 observations read from the data set WORK.APP_EVT. NOTE: PROCEDURE PRINT used (Total process time): real time 5.65 seconds cpu time 5.50 seconds 56 57 58 data assign; 59 set work.assign; ERROR: File WORK.ASSIGN.DATA does not exist. 60 custid = trim(custid); 61 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 60:17 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 60:12 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ASSIGN may be incomplete. When this step was stopped there were 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 62 63 data app_evt; 64 set work.app_evt; 65 custid = trim(custid); 66 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 65:17 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 65:12 NOTE: There were 79691 observations read from the data set WORK.APP_EVT. NOTE: The data set WORK.APP_EVT has 79691 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.03 seconds 67 68 69 rsubmit; NOTE: Remote submit to ACXIOM commencing. 57 data compare; 58 merge work.assign (in=a) work.app_evt (in=b); ERROR: File WORK.APP_EVT.DATA does not exist. 59 by custid; 60 if a and b; 61 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.COMPARE may be incomplete. When this step was stopped there were 0 observations and 181 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: Remote submit to ACXIOM complete. 70 71 proc contents data=mydata;run; ERROR: File WORK.MYDATA.DATA does not exist. NOTE: Statements not processed because of errors noted above. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The SAS System stopped processing this step because of errors.
This part of your code needs to be wrapped in an RSUBMIT step like so:
rsubmit;
proc print data=work.assign;
var custid;
run;
proc print data=work.app_evt;
var custid;
run;
data assign;
set work.assign;
custid = trim(custid);
run;
data app_evt;
set work.app_evt;
custid = trim(custid);
run;
endrsubmit;
The datasets you reference only exist in the remote SAS session, not in your local one.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: