How to add note to Excel when export SAS data?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

How to add note to Excel when export SAS data?

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!


Accepted Solutions
Solution
‎07-07-2014 11:10 PM
SAS Super FREQ
Posts: 8,720

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

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

View solution in original post


All Replies
Valued Guide
Posts: 3,206

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

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

---->-- ja karman --<-----
Esteemed Advisor
Esteemed Advisor
Posts: 7,217

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

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.

Regular Contributor
Posts: 161

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

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."

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

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

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...

Regular Contributor
Posts: 161

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

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!

SAS Super FREQ
Posts: 8,720

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

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;

Attachment
Regular Contributor
Posts: 161

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

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!

Solution
‎07-07-2014 11:10 PM
SAS Super FREQ
Posts: 8,720

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

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

Regular Contributor
Posts: 161

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

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

Valued Guide
Posts: 3,206

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

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.

---->-- ja karman --<-----
Esteemed Advisor
Esteemed Advisor
Posts: 7,217

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

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.

Occasional Contributor
Posts: 8

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

[ Edited ]

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1335 views
  • 3 likes
  • 5 in conversation