Importing and reading data to SAS

[User_2024]

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;

[PaigeMiller]

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.

[User_2024]

Understood, took your suggestion, posted the full error log from where it starts. hope this helps.

[ballardw]

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.

[User_2024]

Thank you for the suggestion. I figured out where the issue is but I am not able to address it. I think, when i merge I try to merge it my dataset from MYLIB and dataset from WORK. I tried to explicitly use work. and mylib. in merge but still I dont see its working. I have pasted my working code and log . Hope this helps.

[User_2024]

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.

[SASKiwi]

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.