topic Re: Disable columns header exporting in named ranges with PCFILES in ODS and Base Reporting

Disable columns header exporting in named ranges with PCFILES

Edoedoedo — Fri, 11 Jul 2014 09:11:34 GMT

Hi,

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:

data THETITLE;

x = "This is a title";

run;

libname xlsout pcfiles server ='xxx' path = 'yyy.xls';

proc sql;

drop table xlsout.TEST;

quit;

data xlsout.TEST;

set TITOLI;

run;

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

Regards

Re: Disable columns header exporting in named ranges with PCFILES

RW9 — Fri, 11 Jul 2014 09:38:05 GMT

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;

run;

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.

Re: Disable columns header exporting in named ranges with PCFILES

Edoedoedo — Fri, 11 Jul 2014 09:42:07 GMT

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?

Re: Disable columns header exporting in named ranges with PCFILES

RW9 — Fri, 11 Jul 2014 09:58:41 GMT

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 / '';

run;

Tagset quick guide: Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

Re: Disable columns header exporting in named ranges with PCFILES

data_null__ — Fri, 11 Jul 2014 11:04:59 GMT

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.

data test;
   x = ' '; 
   label x='This is the First title'; 
   run; 

proc export outfile='~/titletest.xlsx' data=test dbms=xlsx label replace; 
   sheet="Test Title"; 
   run;