topic Re: How to add note to Excel when export SAS data? in SAS Programming

How to add note to Excel when export SAS data?

caveman529 — Mon, 07 Jul 2014 07:04:50 GMT

How to add comments notes (maybe to a different tab) for the SAS data I'm about to export to the Excel file? I find myself frequently confused about which code is used for generating which Excel file. Thank you!

Re: How to add note to Excel when export SAS data?

jakarman — Mon, 07 Jul 2014 07:48:14 GMT

Please explain your confusing and question as it is very confusing what your issue is.....

Re: How to add note to Excel when export SAS data?

RW9 — Mon, 07 Jul 2014 08:26:17 GMT

I suspect he is asking about the functionality in Excel to attach a comment to a cell. I don;t believe this is directly possible through SAS export, however if you wish to put the time into learning Open Office and compile the files yourself you could do it.

For the second part, perhaps putting comments on a separate page, then this is possible. You can use either proc export multiple times to write multiple sheets, or my suggestion would be to use ods tagset.excelxp (search for this, there are many topics on it). Put your data in one dataset, comments in another, then proc report each of them with ods statements to get a multi-tab spreadsheet with data on one sheet and comments on another.

Re: How to add note to Excel when export SAS data?

jakarman — Mon, 07 Jul 2014 10:20:55 GMT

Rw9 than we are back on the OASIS open office specification where Excel xlsx is an estension on. Just some 7 nearly 8 years old but SAS missed that one to do.

The xlsx is based on XML and zip having all attributes including colors been defined. I think it is possible to do also those comment notes. They must be some attributes of a tagset. OASIS Open Office Specification (v1.1 as of 2007). As most interfaces are build as data exchange I do not believe there is a ready solution to fill annotation.

Re: How to add note to Excel when export SAS data?

RW9 — Mon, 07 Jul 2014 10:27:51 GMT

Yep, MS have adopted that format. Its ok, just zipped XML. You are correct though, looking back over the ExcelXP tagset definition there is something called flyover which can do comments. Have a look at the documentation:

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Not sure I would want to go that route myself, however it does look possible.

Re: How to add note to Excel when export SAS data?

caveman529 — Mon, 07 Jul 2014 16:19:21 GMT

Hi, everyone. Thanks for the help here.

I hope to add another worksheet within the Excel file that I can type in whatever notes I want to attach to the Excel file so that I know which Excel file corresponds to which SAS code. It is sort of similar to adding a label but I think SAS dataset label has character length limitation I remember. Thanks!

Re: How to add note to Excel when export SAS data?

caveman529 — Mon, 07 Jul 2014 16:20:50 GMT

Could you tell me how to implement what RW9 suggested?

"For the second part, perhaps putting comments on a separate page, then this is possible. You can use either proc export multiple times to write multiple sheets, or my suggestion would be to use ods tagset.excelxp (search for this, there are many topics on it). Put your data in one dataset, comments in another, then proc report each of them with ods statements to get a multi-tab spreadsheet with data on one sheet and comments on another."

Re: How to add note to Excel when export SAS data?

RW9 — Mon, 07 Jul 2014 17:56:44 GMT

For you 12:19 post, could you perhaps post an example of the output you want. Will take a look tomorrow.

As for the other post, doing the same code multiple times can be done via macro or call execute. E.g.

ods tagsets.excelxp file="...afile.xlsx";

data _null_;

array ds{2} $200. ("SASHELP.CARS","SASHELP.CLASS");

do i=1 to 2;

call execute(' ods tagsets.excelxp options=(sheet_name="'||strip(ds{i})||'");

proc report data='||strip(ds{i})||'; run;');

end;

run;

The above code will produce some code to name an output sheet, then report the data from the dataset out. So you comments could be an empty dataset that you output as a new sheet...

Re: How to add note to Excel when export SAS data?

Cynthia_sas — Mon, 07 Jul 2014 19:12:23 GMT

Hi:

Without going down the SAS Macro Language or CALL EXECUTE road, I prefer to start with simpler syntax. So here's a code example where the first program makes a SAS dataset using a DATA Step program to make the "comment" dataset, with 2 "rows" of comments. Then, the next step is an ODS TAGSETS.EXCELXP step that has 2 PROC REPORT steps inside the ODS "sandwich": 1) printing the data in SASHELP.CLASS and then 2) making a second tab with the comments. Once the file is created, you can keep the existing comments or change them. It really doesn't matter. What is important to realize is that once this file is created, you would probably want to save it under a different name if you intend to edit the file, because if/when you rerun your code, the older version of the file would get overwritten with the newer version of the file. ODS does not "update" existing Excel sheets. The workbook sheet for Comments is shown in the attached screen shot.

Cynthia

** 1) make 2 comments in a SAS dataset;

data mycomments;

length comment $500;

comment = catx(' ','Twas brillig and the slithy toves,',

'did gyre and gimble in the wabe.',

'All mimsy were the borogroves',

'and the mome raths outgrabe.',

'Beware the Jabberwock, my son!',

'The jaws that bite, the claws that snatch.',

'Beware the Jubjub bird and shun',

'the frumious Bandersnatch.');

output;

comment = catx(' ', 'xxxxxxxx10', 'xxxxxxx20', 'xxxxxxx30',

'xxxxxxx40', 'xxxxxxx50', 'xxxxxxx60', 'xxxxxxx70',

'xxxxxxx80', 'xxxxxxx90', 'xxxxxx100',

'xxxxxxxx10', 'xxxxxxx20', 'xxxxxxx30',

'xxxxxxx40', 'xxxxxxx50', 'xxxxxxx60', 'xxxxxxx70',

'xxxxxxx80', 'xxxxxxx90', 'xxxxxx200',

'xxxxxxxx10', 'xxxxxxx20', 'xxxxxxx30',

'xxxxxxx40', 'xxxxxxx50', 'xxxxxxx60',

'xxxxxxx70', 'xxxxxxx80', 'xxxxxxx90', 'xxxxxx300');

output;

run;

**2) send the output to Excel using TAGSETS.EXCELXP;

** but first display some data, like from SASHELP.CLASS;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\withcomments.xml' style=sasweb

options(embedded_titles='yes' doc='Help' sheet_name='Data');

proc report data=sashelp.class nowd;

title '1) My data';

column name age sex height weight;

run;

ods tagsets.excelxp options(sheet_name='Comments'

row_heights='0,40,0,40,0,0,0');

proc report data=mycomments nowd;

title '2) My Comments';

column comment;

run;

ods _all_ close;

Re: How to add note to Excel when export SAS data?

caveman529 — Tue, 08 Jul 2014 00:17:10 GMT

Thank you so much, Cynthia! This is magic!

I'm being a little greedy here: I usually send my resulting SAS dataset to Excel at the end of my SAS code within the SAS Enterprise Guide. It it possible to "send" the SAS code to the Excel "code" worksheet? Thank you!

Re: How to add note to Excel when export SAS data?

Cynthia_sas — Tue, 08 Jul 2014 03:10:51 GMT

Hi:

I'm not entirely sure what you mean by "sending the SAS code to the Excel code sheet". If you know the name of the SAS code file, then yes, you can read it and "print" it to a sheet in the Excel file. However, if EG is creating the code on the fly and you are just running the code created in the project, then that is a harder task.

Consider the following program, stored in a file called c:\temp\mycodefile.sas (without ANY ODS statements in the code):

proc print data=sashelp.class;
title 'Sending SASHELP.CLASS to Excel';
run;

proc freq data=sashelp.class;

tables age;

run;

Now, you need to read this program into a SAS dataset so you can "print" or display it using PROC PRINT or PROC REPORT:

data thiscode;

length pgmline $256;

infile "c:\temp\mycodefile.sas" length=lg;

input pgmline $varying. lg ;

pgmord = _n_;

run;

And then you need to use PROC PRINT or PROC REPORT to display what is in the WORK.THISCODE dataset and get it into a worksheet. Not that it's hard, but it is a lot of overhead to go to in order to put your code into the Excel worksheet. My tendency is to follow a naming convention, like putting the program name in a footnote or naming the report the same name as the program that created it. I usually don't want the people who see my reports to see my code.

cynthia

Re: How to add note to Excel when export SAS data?

caveman529 — Thu, 10 Jul 2014 03:23:00 GMT

Hi, Cynthia: Thank you so much! Yes, I just want to send my manually-coded SAS code to the Excel. Your code works great!

Re: How to add note to Excel when export SAS data?

Unito — Tue, 29 Mar 2016 20:45:22 GMT

I managed to do this in this manner:

1) put the comments in a dataset

2) export the comments to an Excel worksheet

I used the code below (dequote and resolve are used to properly handle macro variables).

What is your opinion on this method?

data explanation (drop=text);

format contents $1000.;

input text $80.;

contents=dequote(resolve(quote(text)));

datalines;

Description of the contents

---------------------------------------------------------------------

file created by &sysuserid.

this xlsx file contains data about

important stuff

;

run;

proc export data= work.explanation

outfile="c:\myexcelfile.xlsx"

dbms=xlsx replace; putnames=no;

sheet=explanation;

run;

29-3-2016 addition: I discovered some improvements. I might be better to use sql and insert to generate the dataset.