BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
lyth_abb
Fluorite | Level 6

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.

image2.png

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:

image1.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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).

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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.

lyth_abb
Fluorite | Level 6
Oh I see, many thanks Kurt_Bremser , so what should be replaced for 'WORK' if I wanna keep the table created?
Kurt_Bremser
Super User

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).

lyth_abb
Fluorite | Level 6
many thanks @Kurt_Bremser, I will work on with SAS admin to sovlve this problem

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1376 views
  • 1 like
  • 2 in conversation