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

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

imdickson — Tue, 04 Apr 2017 07:05:26 GMT

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.

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

RW9 — Tue, 04 Apr 2017 09:02:55 GMT

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.

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

Oligolas — Tue, 04 Apr 2017 13:46:38 GMT

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);

Cheers

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

ballardw — Tue, 04 Apr 2017 14:27:14 GMT

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.

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

XinxinWei — Thu, 01 Mar 2018 08:20:59 GMT

hi, if I add a variable flag_2;

data aaa;
set sashelp.class;
order=_n_;
if name="Barbara" then do;
flag_2="Y";
comment_2="Her name is Barbara";
end;
if name="Jane" then do;
flag_3="Y";
comment_3="Her Gender is Female";
end;
if name = "Carol" then flag_new="New";
flag_1="";
flag_4="";
flag_5="";
run;



proc report data=aaa;
columns age name sex height weight flag_2;
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'];

define flag_2/noprint;

compute name; 
* Cell comment; 
if flag_2="Y" and name^="" then call define(_col_, "style", "style=[background=green flyover='Her name is Barbara']");
/*this code indicate that if the flag_2 is Yes, I will let the cell get the green color and add a comment "Her name is Barbara"*/
endcomp;
run;

why doesn't the code work? the cell for name="Barbara" DON'T get the green color

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

KSASW — Fri, 26 Jan 2024 15:37:25 GMT

Put flag_2 before name in the column statement.