topic Re: SAS job schedule run successfully but not return the output (new table) in SAS Programming

SAS job schedule run successfully but not return the output (new table)

lyth_abb — Fri, 11 Aug 2023 04:51:33 GMT

Hi SAS expert team,

I have scheduled a job (hourly) from a sas program to create new library and new table. This job ran successfully even when i'm not login sas, and after this job ran successfully, I login sas and cannot see the new library (CE_DATA)/newtable which should be created by the sas program. Can anyone please explain to me what's wrong here? Many thanks.

For more information, this sas program is simply a code to:

create new library 'CE_DATA'
create connection to oracle
create new table from sql/oracle database and then do some transpose, left join

as below:

/*Get data CAR credit rating from oracle*/
proc sql;    

libname CE_DATA "%sysfunc(pathname(work))";

connect to oracle as myconn 
(user="" 
password="" 
encoding="UTF8" 
path=''); 

create table CE_DATA.newtable as
select * from connection to myconn       
    (
		select  
		b.carid, b.borrowercustomercode, b.attribute, b.value

		from
		(select a.* , row_number() over (partition by a.carid order by a.carid,a.decisionid desc) row_num
		from app_ce_dh.dm_output_decision_history a) b
		
		where 1=1
		and row_num <=32
		and carid like 'CAR%'
		and attributegroup in ('MODEL_RESULT','DECISION')
	) ;  

disconnect from myconn; 

proc transpose data=CE_DATA.newtable out=CE_DATA.newtable2;
  by CARID BORROWERCUSTOMERCODE  ;
  id ATTRIBUTE ;
  var VALUE ;
run;

/*Get status of CAR from app_mhrr */
proc sql;    

connect to oracle as myconn 
(user="" 
password="" 
encoding="UTF8" 
path=''); 

create table CE_DATA.trangthai as
select * from connection to myconn       
    (
		select a.application_id, a.appl_status,a.Day_id,b.finalstatus 
		
		from app_mhrr.rf_dim_app_process a 
		Left join 
		(select application_id,max(day_id) as finalstatus from app_mhrr.rf_dim_app_process group by application_id) b
		on a.application_id = b.application_id and a.day_ID = b.finalstatus
		
		where b.finalstatus is not null 
		and a.application_id like 'CAR%'
		group by a.application_id, a.appl_status,a.day_id,b.finalstatus
	) ;  

disconnect from myconn; 

/*left join status of CAR*/

proc sql;
	create table final_table as
	select * from newtable2 as x left join trangthai as y
	on x.carid = y.application_id;
quit;

I ran this sas program mannually (without job schedule) and it is completed successfully, and then I saw the library and the table that I want like this screen:

Re: SAS job schedule run successfully but not return the output (new table)

Kurt_Bremser — Fri, 11 Aug 2023 05:59:20 GMT

What does the log say (Maxim 2)?

Re: SAS job schedule run successfully but not return the output (new table)

Kurt_Bremser — Fri, 11 Aug 2023 06:03:26 GMT

Oh, I see your mistake.

You create the library as just another name for WORK:

libname CE_DATA "%sysfunc(pathname(work))";

Since the directory containing WORK is physically deleted upon termination of the SAS session, all data in it will vanish.

Re: SAS job schedule run successfully but not return the output (new table)

lyth_abb — Fri, 11 Aug 2023 06:36:35 GMT

Oh I see, many thanks Kurt_Bremser , so what should be replaced for 'WORK' if I wanna keep the table created?

Re: SAS job schedule run successfully but not return the output (new table)

Kurt_Bremser — Fri, 11 Aug 2023 07:28:41 GMT

You need to use an existing library for which you have write permission (talk to your SAS admins), or create one in a location where you have write permission (e.g. in your home directory on the SAS server).

Re: SAS job schedule run successfully but not return the output (new table)

lyth_abb — Mon, 14 Aug 2023 03:08:28 GMT

many thanks @Kurt_Bremser, I will work on with SAS admin to sovlve this problem