BookmarkSubscribeRSS Feed
Edoedoedo
Pyrite | Level 9

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Edoedoedo
Pyrite | Level 9

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

data_null__
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1486 views
  • 6 likes
  • 3 in conversation