The SAS Output Delivery System and reporting techniques

ODs EXCEL: I want my files in my directory, not in a temp folder

Reply
Contributor
Posts: 51

ODs EXCEL: I want my files in my directory, not in a temp folder

Hi to all

 

I'm using SAS 9.4M5 / SAS EG 7.15 / SAS Studio 3.7 - I have the same problem, whatever the platform.

 

when creating an Excel file with ODS Excel, even if I indicate the location and the name of the XLSX file I want to create :

ODS Excel File="c:/myfolder/mysubfolder/myfile.xlsx" ;

the file is created but written in a temp folder, not in the folder I wanted :

SAS EG : C:\users\me\AppData\Local\Temp\SEG...

SAS Studio and SAS 9.4 : C:\users\me\AppData\Local\Temp\SAS Temporary Files\...

 

I tried to use a FILENAME:

FILENAME rez "c:/myfolder/mysubfolder/myfile.xlsx" ;
ODS Excel File=rez ;

no difference... and no such problem with ODS RTF, PDF, POWERPOINT and HTML

 

any clue?

 

Best regards

 

Sébastien

Contributor
Posts: 51

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

but no problem with SAS University Edition...

SAS Super FREQ
Posts: 9,434

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Hi:

  I do not observe that behavior. When I run code using my local version of SAS using EG or SAS Studio or Display Manager, I do get the XLSX file created in my C: drive folder.

 

Using EG:

xlsx_Made_EG.png

 

 

Using SAS Studio (and you can see the SAS Display Manager file created in this screen shot too)

xlsx_Made_local_Studio.png

 

My experience with EG is NOT to click the tempting Download button on the EG Results tab. I always go to Windows Explorer and open the file by clicking there or open Excel and do a File --> Open from there.

 

Hope this helps,

Cynthia

Contributor
Posts: 51

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Posted in reply to Cynthia_sas

Thanks Cynthia for your input.

 

I think there is a ghost in the machine for me...

 

I restarted my session, and tried again to put my XLSX file in my directory. This time, it works.

 

Go figure…

 

But I still have problems with ODS EXCEL… see this program

 

data class;
   set sashelp.class;
   calc1="=RC[-1]/RC[-2]";
   length calc2 $ 8 ;
   calc2="=F"||cats(_n_+1)||"/E"||cats(_n_+1);
run;

ODS EXCEL ;
   proc print data=class;
   run;
ODS EXCEL close ;

The calc1 formula in R1C1 notation creates a damaged Excel file while the calc2 formula, in A1 notation, works.

 

But this is another problem I guess…

 

(I’m not looking for an alternative solution, I’m just trying to build a very simple example to show the way things works)

 

Best regards

 

Sébastien

SAS Super FREQ
Posts: 9,434

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Hi:

  I have only sent formulas into ODS EXCEL using TAGATTR and formula: in a STYLE= override. I am not sure about sending them in as variable values. The doc http://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVer... shows using TAGATTR. Have you tried that?

 

cynthia

Contributor
Posts: 51

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Posted in reply to Cynthia_sas

Hi,

 

I am not sure about sending them in as variable values.

 

This is the way things work with the EXCELXP tagset.

 

This is the part of the help file relating to the FORMULAS sub option for the EXCELXP tagset :

Formulas:   Default Value 'yes'
     Values: yes, no, on, off.
     By default, data values that start with an '=' will become formulas
     instead of cell values.  This behavior can be turned off by setting
     this option to 'no'.  Excel only understands relative column references
     in it's XML.  A formula like sum(C2,C3) or A2+B3 will not work.
     An equivalent might be sum(R[-2]C,R[-1]C) or RC[-2]+RC[-1].
     See the Proc Print example under Default_Column_Width.

With this program, the CALC1 is correctly constructed (while the CALC2 is not, as said by the documentation) :

 

data class;
set sashelp.class;
calc1="=RC[-1]/RC[-2]";
length calc2 $ 8 ;
calc2="=F"||cats(_n_+1)||"/E"||cats(_n_+1);
run;

ODS tagsets.excelxp file="c:/intro_sas/ods/ex3.xml";
proc print data=class;
run;
ODS tagsets.excelxp close ;

This FORMULAS sub-option is described this way in the ODS EXCEL help:

 

(FORMULAS= 'OFF' | 'ON')

specifies if data values that begin with an '=' become formulas or cell values.

ON

data values that begin with an '=' become formulas.

Alias YES

OFF

data values that begin with an '=' become cell values.

 

well... since the sub option is having the same name between ODS EXCEL and the EXCELXP tagset, I expected ODS EXCEL to act with formulas the same way the EXCELXP tagset did.

 

This paper: https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf (page 10), seems to confirm that A1 and R1C1 notations are accepted.

 

Since, with ODS EXCEL, a formula in A1 notation is accepted,

since, with the EXCELXP tagset, R1C1 notations were accepted

I wonder why R1C1 notations are not accepted with ODS EXCEL the way they were accepted by the EXCELXP tagset.

 

and yes, TAGATTR could do this job... but... between you and me, would you tell me that tagattr is very easy to use ? ;-)

 

best regards

 

Sébastien

 

SAS Super FREQ
Posts: 9,434

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Hi,

 

  This program works for me in both TAGSETS.EXCELXP and ODS EXCEL using TAGATTR:

title; footnote;

  ods excel file='c:\temp\formula_xlx.xlsx';
  ods tagsets.excelxp file='c:\temp\formula_xp.xml' style=htmlblue;

  proc report data=sashelp.prdsale spanrows;
     where country eq 'CANADA' and year eq 1993;
     column country prodtype product actual predict diff;
	 define country / group;
	 define prodtype / group;
	 define product / group;
     define predict / sum style(column)={tagattr='format:Currency'};
     define actual / sum style(column)={tagattr='format:Currency'};
     define diff / computed 
            style(column)={tagattr='format:Currency formula:RC[-1]-RC[-2]'};
     compute diff;
	   diff=0;
	 endcomp;
  run;

  ods tagsets.excelxp close;
  ods excel close;

As you can see, on the SAS side, I am assigning a value of 0 to DIFF, but in TAGATTR, I am using a formula that will subtract the Actual and Predict values. So, even with the assignment of 0 at the SAS end, when I open either sheet in Excel, the column for DIFF is showing the impact of the formula:

predict_minus_actual_ods_excel.png 

 

I do see the documentation about formulas in ODS EXCEL, but since I rarely send formulas from SAS to Excel, I've never had an occasion to test it using ODS EXCEL. I don't know the answer to this question: "I wonder why R1C1 notations are not accepted with ODS EXCEL the way they were accepted by the EXCELXP tagset." My guess is that the XML is different between Spreadsheet Markup Language 2003, as generated by EXCELXP and the newer .XLSX XML format generated by ODS EXCEL, but that is just a guess.

 

  As for your other question... "but... between you and me, would you tell me that tagattr is very easy to use ? ;-)" I guess I wouldn't put TAGATTR in the same "very easy" category as NOOBS or SPLIT options at the procedure level, but TAGATTR is just a STYLE override and, style overrides are handled by the rendering destination. In this case, the rendering destination is Excel. And, once you understand how style overrides work, I think that they are not difficult -- I wrote this paper https://support.sas.com/resources/papers/proceedings11/266-2011.pdf to explain TAGATTR specifically with ODS TAGSETS.EXCELXP and ODS MSOFFICE2K -- but that paper didn't go into formats and it was written before ODS EXCEL.

 

  Sorry I don't have a better answer to your first question.

 

Cynthia

 

 

Contributor
Posts: 51

Re: ODs EXCEL: I want my files in my directory, not in a temp folder

Posted in reply to Cynthia_sas

Hi,

My guess is that the XML is different between Spreadsheet Markup Language 2003, as generated by EXCELXP and the newer .XLSX XML format generated by ODS EXCEL, but that is just a guess.

 

I do agree.. this problem is certainly a XML problem...

 

I wrote this paper https://support.sas.com/resources/papers/proceedings11/266-2011.pdf to explain TAGATTR

I'm a fan of evything you wrote ;-)

 

I'll contact the support about the problem I raised. I'll update this post when I'll have answers ;-)

 

best regards

 

Sébastien

 

Ask a Question
Discussion stats
  • 7 replies
  • 249 views
  • 0 likes
  • 2 in conversation