DATA Step, Macro, Functions and more

Can i export data into excel with Comments in each cell?

Reply
Frequent Contributor
Posts: 75

Can i export data into excel with Comments in each cell?

Hi guys, I have a set of data containing the description, start date and end date in a table format, similar to excel. The previous developer set the data to export into excel file. However, my client wants to have comment box when hover to a particular cell in excel. May i know if this is possible? The comment box will display the secription in my data.

 

I need your advice.

Super User
Super User
Posts: 7,432

Re: Can i export data into excel with Comments in each cell?

No, not directly.  Personally, If I have to create an Excel file, with Excel specific functionality (and that would follow a lot of calls about why Excel isn't a good medium for data), then I would dump the data out to a plain Excel file, and have a macro in VBA which loads and processes the data using Office VBA.  Its very simple, most taks you can do and record to get the code.

Frequent Contributor
Posts: 104

Re: Can i export data into excel with Comments in each cell?

[ Edited ]

Hi,

 

you can control a lot of things within SAS, comment boxes and highlights are possible, check this out:

ODS LISTING CLOSE;
ods TAGSETS.EXCELXP file="C:\TEMP\workbook.xml" style=statistical
                    options(frozen_headers='yes' sheet_interval='none' sheet_name="class" autofilter='1-8' autofit_height='yes');

proc report data=sashelp.class;
   columns age name sex height weight;
   define age    /display style=[just=l cellwidth=2.0cm tagattr='type:String format:0'];
   define name   /display style=[just=l cellwidth=2.0cm tagattr='type:String format:Text'];
   define sex    /display style=[just=l cellwidth=2.0cm tagattr='type:String format:Text'];
   define height /display style=[just=l cellwidth=2.0cm tagattr='type:String format:0'];
   define weight /display style=[just=l cellwidth=2.0cm tagattr='type:String format:0'];

   compute weight; 
      * Cell comment;  
      if      (weight/2)/(height*2.54/100)**2<18.5        then call define(_col_, "style", "style=[background=CXFFFF66 flyover='Underweight']");
      else if  18.5<=(weight/2)/(height*2.54/100)**2<25   then call define(_col_, "style", "style=[background=white flyover='healthy weight']");
      else if  (weight/2)/(height*2.54/100)**2>=25        then call define(_col_, "style", "style=[background=CXFFCC66 flyover='Overweight']");

   endcomp;
run;

ods tagsets.excelxp close;
ods listing;

A way to convert back to native xlsx can be found here: http://support.sas.com/kb/43/496.html

But in order to keep your comment on the cells you first need to convert the xml to xls and afterward the xls to xlsx. A very quick and even more dirty way to achieve this is:

options noxwait xsync; /* cave: execute synchronously with SAS */
%macro convertXML2Xls(default=); %local ext; %let ext=xml; data _null_; file "'&default\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&default"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; put " "; put "for each f in myfiles"; put " ExtName = fso.GetExtensionName(f)"; put " Filename= fso.GetBaseName(f)"; put " if ExtName=""&ext"" then"; put " set mybook = xlapp.Workbooks.Open(f.Path)"; put " xlapp.Visible = false"; put " mybook.SaveAs ""&default.\"" & Filename & "".xls"", -4143"; put " End If"; put " Next"; put " mybook.Close"; put " xlapp.DisplayAlerts = True"; /* Removes original files */ /*put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";*/ put " xlapp.Quit"; put " Set xlapp = Nothing"; put " strScript = Wscript.ScriptFullName"; put " FSO.DeleteFile(strScript)"; run; x "cscript ""&default\temp.vbs"""; %mend; %convertXML2Xls(default=c:\temp); %macro convertXls2XlsX(default=); %local ext; %let ext=xls; data _null_; file "'&default\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&default"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; put " "; put "for each f in myfiles"; put " ExtName = fso.GetExtensionName(f)"; put " Filename= fso.GetBaseName(f)"; put " if ExtName=""&ext"" then"; put " set mybook = xlapp.Workbooks.Open(f.Path)"; put " xlapp.Visible = false"; put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51"; put " End If"; put " Next"; put " mybook.Close"; put " xlapp.DisplayAlerts = True"; /* Removes original files */ /*put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";*/ put " xlapp.Quit"; put " Set xlapp = Nothing"; put " strScript = Wscript.ScriptFullName"; put " FSO.DeleteFile(strScript)"; run; x "cscript ""&default\temp.vbs"""; %mend; %convertXls2XlsX(default=c:\temp);

 

screenshot_35.pngCheers

________________________

- Cheers -

Super User
Posts: 10,552

Re: Can i export data into excel with Comments in each cell?

Not with Proc Export. Proc Export is designed to be a bare-bones data interchange. The report procedures allow adding additional bits as @Oligolas demonstrates.

Ask a Question
Discussion stats
  • 3 replies
  • 137 views
  • 0 likes
  • 4 in conversation