07-11-2014 05:11 AM
I am working in a very limited environment, I must use PCFILES because SAS is running on a Unix 64bit machine, and my PC uses Windows 7 32bit.
I want to write for instance a single value in a single excel cell. Therefore I prepare a template excel file, where I create e.g. in the cell F2 a named range "TEST". Then I do:
x = "This is a title";
libname xlsout pcfiles server ='xxx' path = 'yyy.xls';
drop table xlsout.TEST;
libname clear xlsout;
After this, in the F2 cell I get the columnname 'x' and in F3 the value "This is a title". Is there a way to suppress columns header to export direcly the values in the named range?
Thanks a lot
07-11-2014 05:38 AM
Not sure what pcfiles is. I would recommend that you use tagsets.excelxp and proc report as your going to have far more control over all parts:
ods tagsets.excelxp file="...\afile.xls";
title1 "This is a title";
proc report data=some_data style=statistical;
column x y z;
ods tagsets.excelxp close;
You should be able to run that code in any environment as what it generates is XML, which Excel can then interpret, but is a text file so all systems will be able to generate and open.
07-11-2014 05:42 AM
Thank you, but this put the title in cell A1 while we would like to put it in any other cell like F2. Can it be done with tagset.excelxp?
07-11-2014 05:58 AM
There's no inbuilt function to do that anywhere in SAS as far as I know. The reason is simple, SAS is a structured data format, where there are columns with headers and data down the page. Excel has the concept of anything goes which is its biggest plus and negative point. What you are talking about is not a "Title", but text somewhere in the data region. If you could provide a small example of what you want, you could manipulate your data to contain this. For example if I have:
A B C
1 2 3
4 5 6
7 8 9
And I want some text in cell D2, then I would update my dataset as:
A B C D
1 2 3
4 5 6 Some text.
7 8 9
Then in my report, set the label for variable D to be missing:
proc report data=...;
column a b c d;
define a / 'sfafsa';
define d / '';
Tagset quick guide: Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset
07-11-2014 07:04 AM
You might try using the variable label as the text you want to display. I don't have PCFILES to test but this PROC EXPORT is similar to what I think you might be able to do.