As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Modifying SAS Explorer's "View in Excel"

From sasCommunity
Jump to: navigation, search

Changing the functionality of 9.2 SAS Explorer View in Excel


Problem: SAS users often work with various files from many sources in combination and so it's important to have utilities that can quickly look at the data and do things like retrieve the column headers. SAS Explorer's View in Excel works well for opening small datasets and for quickly look at the data or for copying the column names.

Two problems though, medium-to-large datasets load too slowly or are too large, and with version 9.2 SAS began showing the column headers rather than the variable names. Thus the task is to create a new selection on the right-click pop-up below View in Excel to be named View 500 Excel.

Solution: First locate the SCL entry for View in Excel. Find the catalog location from the View in Excel reference at (tools) (options) (explorer) (members) (table) to be at SASHELP.EXPLORER.EXCEL_TABLE_OPEN.SCL.

Then go to that catalog folder via (SASHELP) (EXPLORER), open the entry EXCEL_TABLE_OPEN, and copy the SCL code. Tweak the code to have OBS=500 during the write process and then back to OBS=MAX. Also remove the PROC PRINT LABEL option:


original snippet:

submit immediate;
      filename _temp_ "&tpath";
      ods noresults;ods listing close;
      ods html file=_temp_ rs=none style=minimal;
      proc print data=&library.'&table'N label noobs;run;ods html close;
      ods results;ods listing;
      filename _temp_;
      dm "winexecfile ""&tpath"" ";
endsubmit;

modified snippet:

submit immediate;
      options obs=500;
      filename _temp_ "&tpath";
      ods noresults;ods listing close;
      ods html file=_temp_ rs=none style=minimal;
      proc print data=&library.'&table'N noobs;run;ods html close;
      ods results;ods listing;
      filename _temp_;
      dm "winexecfile ""&tpath"" ";
      options obs=max;
endsubmit;


Next, create a writeable catalog in your SASUSER folder (right click in SAS Explorer window (New...)) called MenuItems and add an SCL program entry (again (New...)) named Excel500 containing the modified code. Save the SCL and compile it (right click (Compile)). A compiled version of this catalog entry has been uploaded to sasCommunity and can be downloaded see File:MenuItems.sas7bcat.


Finally, to add the selection to the pop-up menu, go back to (tools) (options) (explorer) (members) (table) and added a new menu item name View 500 Excel with the correct SCL reference: AFA C=SASUSER.MenuItems.EXCEL500.SCL LIBRARY='%8b' TABLE='%32b';


Result: Now, when a table is right-clicked in SAS Explorer the selection View 500 Excel fires off Excel500 and loads the first 500 rows with standard column names into Excel for a quick view.



Thanks to Richard DeVenezia for inspiration.


....read more

Article written by Pchoate