Architecting, installing and maintaining your SAS environment

Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Reply
Community Manager
Posts: 352

Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Do you have a burning administration or deployment question? Need help figuring out what that error message means? Have an installation conundrum? Have a metadata, server or directory question you would ask at SAS Global Forum, if only you were attending? Well, now is your time!

 

Coaching.png

SAS employees from R&D, product management, technical support and customer loyalty will be on hand to answer your questions in near real-time. Just sign in to communities.sas.com, head to the Administration community and post your question. We will be standing by to provide helpful advice, ask follow on questions, or direct you to the appropriate resources.

 

Remember: it’s one hour only, 2:00-3:00pm ET on March 8, for the near real-time response; but, as you know there’s always someone here to help.

 

We’ll see you then!

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

[ Edited ]

Hello,

 

I have a pre-question for this event:

 

This code works great in SAS EG but when placed on the server, it kicks back this error message (in red):

 

%include '/sas/shared/628562/pass.sas';

libname DVM oracle user=&user_a password=&pass_a path=DVMTFPRD schema=APP_ALL;

 

data _null_;

date1=today();

call symput ('paydate',put(intnx('day',date1,-2,'end'),date11.));

call symput ('busdate',put(intnx('day',date1,-1,'end'),date11.));

run;

%put &busdate;

%put &paydate;

 

proc sql;

connect to oracle as oracle (user=&user_a password=&pass_a path=DVMTFPRD);

create table aggregate1 as select distinct * from Connection To oracle

(select

a.Business_dt,

a.Next_Pmt_Dt,

a.sale_dt,

a.service_entity_num,

a.service_entity_nm,

a.contract_num,

a.member_num,

a.fac_cd,

a.suppression_cd,

a.Loan_and_Credit_Life_Bal_Amt

 

from vh_coll_contract_hist a inner join

dm_d_calendar b

on a.report_category_nm = b.report_category_nm

and a.business_dt = b.business_dt

 

where b.region_loaded_cd in ('NA','ALL')

and a.report_category_nm in ('DAILY', 'SEMI-MONTHLY')

and a.Next_Pmt_Dt between '15-jan-2017' and %bquote('&paydate.') /** We started the project on Jan 15 **/

and trunc(a.business_dt) = %bquote('&busdate.')/** business date is yesterday **/

and a.service_entity_nm in ('FAIRFIELD','TRENDWEST')

and a.delete_crmast_ind = 'N'

and a.suppression_cd in ('7','%','A','C','H','M','N','O','S')

and a.fac_cd = 'Q'

and a.loan_and_credit_life_bal_amt >0

and a.Contract_Status_Cd = '0'

);

Disconnect from oracle;

quit;

 

data aggregate2;

set aggregate1;

 

dayslate = intck('day',datepart(next_pmt_dt),datepart(business_dt));

run;

 

proc sort data=aggregate2;by dayslate;run;

 

proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'

dbms=xlsx replace ;

sheet = "&busdate.";

run;

 

/*

filename mymail email ("adminemail@abc.com")

subject="Pool Code Q Project Daily file"

attach=("/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx" content_type="application/xlsx");

 

data _null_;

file mymail;

put 'All,';

put "Here is the daily file as of &busdate. for the Pool Code Q project. Please let me know if you have any questions on the data.";

put;

put 'Thank you,';

put 'Max';

run;

*/

 

filename mymail email ("firstname.lastname@abc.com" "firstname2.lastname2@abc.com" "firstname3.lastname3@abc.com"

"firstname4.lastname4@abc.com" "firstname5.lastname5@abc.com" "firstname6.lastname6@abc.com" )

subject="Pool Code Q Project Daily file"

attach=("/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx" content_type="application/xlsx");

 

data _null_;

file mymail;

put 'All,';

put "Here is the daily file as of &busdate. for the Pool Code Q project. Please let me know if you have any questions on the data.";

put;

put 'Thank you,';

put 'Max';

run;

 

 

 

 

62 proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'

63 dbms=xlsx replace ;

64 sheet = "&busdate.";

65 run;

 

Error creating temporary file for XLSX file -> /sas/shared/mfritz/SAS/Data/pool_code_Q//Pool_Code_Q.$$1 . It is either not an

Excel spreadsheet or it is damaged. Error code=8000101D

Requested Output File is Invalid

ERROR: Export unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.21 seconds

cpu time 0.01 seconds

 

Any thoughts?

 

Thanks,

Sharda

 

Trusted Advisor
Posts: 1,253

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

FYI, I have edited your post to remove the email addresses.

 

When you say the code is "placed on the server" do you mean it is run by a SAS service account and not by your identity? It could be permissions related for how it is being executed by the service account not having access.

 

Kind Regards,

Michelle

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Thank you for editing the email addresses...

 

 

The code is placed on the grid server as a scheduled job.


Sharda

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

I have two scheduled Jobs on the Grid kicking back this error message based on the code snippets below:

 

61
62 proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'
63 dbms=xlsx replace ;
64 sheet = "&busdate.";
65 run;

 

 

Error creating temporary file for XLSX file -> /sas/shared/mfritz/SAS/Data/pool_code_Q//Pool_Code_Q.$$1 . It is either not an
Excel spreadsheet or it is damaged. Error code=8000101D
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.21 seconds
cpu time 0.01 seconds

 

 

-----

 

542 proc export data=both_twodays_final
542 ! outfile="/sas/shared/mfritz/SAS/Data/auto_pay/daily_autopay/CS_reconciliation_data/reconciliation_data_&yesterday5..xlsx"
543 dbms=xlsx replace ;
544 sheet = "Onbothdays_twodaysago";
545

Error creating temporary file for XLSX file ->
/sas/shared/mfritz/SAS/Data/auto_pay/daily_autopay/CS_reconciliation_data//reconciliation_data_06MAR.$$1 . It is either not an
Excel spreadsheet or it is damaged. Error code=8000101D
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.13 seconds
cpu time 0.02 seconds

 

What do you think is the issue?

 

Thanks,
Sharda

SAS Employee
Posts: 99

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

I would verify that you have available disk space in the outfile location you're specifying.  You could also try redirecting to a different output location (with available space).  If these don't help, I believe you're best served taking this one to Tech Support.

PROC Star
Posts: 1,095

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

My first thought is to agree with Michelle, because I always suspect permission first.

 

Since you mention Grid, though, the other thing that comes to mind is a problem relating to shared file system:

1. Could jobs on different nodes be trying to access this file for update, and not being serialized correctly?

2. Could things be set up wrong, and this directory being incorrectly accessed for update by multiple grid nodes?

 

I will confess, I'm really spitballing here. It just seems like such an odd error if it's working correctly in EG.

 

Tom

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Thank you all but I have confirmation that the service account has permission to that shared drive...

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari - what do you mean by your first point (specifically not being serialized)?

 

Thanks

PROC Star
Posts: 1,095

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

I'm probably using the terminology wrong, because I'm far from an expert.

 

But the job of the shared file server is to make sure that any disk resource (a directory name, a file name, a piece of physical disk) is created / updated by only one job, user, whatever.

 

If one job was busily writing the temporary file that's the source of an error message, and then another job was able to get a hold of the same resource for update, and make some changes, the first job would think that the file was corrupted. What should happen is the second job should be told that the resource is locked for update by another job.

 

As I say, spitballing.

PROC Star
Posts: 1,095

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

I should have added: If it works in EG in a non-grid environment, it leads me to wonder what's different in grid? And the shared file system is a very large elephant in the room.

 

Another thought. Can you run a test that works in the EG environment, and then run EXACTLY THE SAME TEST, same code, same data, same everything, on ONLY ONE GRID NODE? It would be interesting to see if it fails with the same message.

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Since we have only a production environment, i would need to wait until I can test this during our next outage but thanks for the suggestion.

Trusted Advisor
Posts: 1,141

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Hello @sharda,

 

I have some questions:

- does it happen only on some jobs?

- does it happen  with all the jobs that include a proc export? 

- on the jobs that it happens: does it happens always you run them, or only sometimes?

- what happens if you select xls intead of xlsx?

Contributor
Posts: 40

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

it happens specifically on the two jobs I added the snippet from which both included a proc export. Also, it happens sometimes but enough to stop the job from completing and the user being angry. I asked the user to test the xls instead but he is tied up at the moment.

 

Thanks though

Trusted Advisor
Posts: 1,141

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Ok, I see.

 

I have seen previosly similar kind or errors. The most common are related to:

- too large amount of rows on the exported excel

- incorrect template used

- some kind of problem on the storage, generally on shared storages, very common on Grid environments. On the direction of what @TomKari rightly indicated

 

The first 2 cases are easier to solve, but the last one usually requires intervention from SAS Consultancy services ot SAS Technical Support, because requires hands on investigation.

 

Let;s wait for the test done by your user. Thank you.

Ask a Question
Discussion stats
  • 23 replies
  • 1131 views
  • 18 likes
  • 6 in conversation