Automating Excel pivot table in SAS

Reply
Contributor
Posts: 31

Automating Excel pivot table in SAS

My SAS program generates an excel with 1 year of data and i manually paste the data into a pivot table (excel) and send it to users. We need to automate it and looking if it can be done in SAS.

Super User
Posts: 10,549

Re: Automating Excel pivot table in SAS

My first idea whenever I see 'pivot table' is to make the table in SAS and export that.

Trusted Advisor
Posts: 1,204

Re: Automating Excel pivot table in SAS

You can try combination of ods tagsets and proc tabulate to do that.

Super User
Posts: 17,950

Re: Automating Excel pivot table in SAS

Yes it can.

How - there are many different ways.

One is to replicate exactly what you're doing through DDE - which is considered outdated technology but still useful in my opinion.

Another is to duplicate the results exactly and export to Excel via ODS, tagsets.excelxp.

And another is to use ODS TableEditor which mimics the Pivot table a bit better, but is ugly (visually) in my opinion.

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Base SAS: Creating a Data Grid Like VB.NET

Pivot Tables - Build in Excel, export out SAS data set, use DDE to force refresh

Contributor
Posts: 31

Re: Automating Excel pivot table in SAS

Hello Reeza,

This is the code from the discussion you have attached.

/* Tell SAS to open Excel */

options noxwait noxsync;

data _null_;

     rc=system('start excel');

     rc=sleep(10);

run;

/* Execute VBA code via SAS DDE - open file, select sheet with pivot, put cursor on pivot table and refresh pivot table */

filename cmds dde 'excel|system';

data _null_;

     file cmds;

     put "[open(""\\&path\&f_name..xlsx"")]"; 

     put '[workbook.activate("Pivot")]';

     put '[select("R1C1")]';

     put '[pivot.refresh()]';

     put "[save()]";

     put "[quit()]";

run;

quit;

Could you please explain the above code or point me any link where i can get better explanation.

Super User
Posts: 17,950

Re: Automating Excel pivot table in SAS

This is the DDE code to automatically refresh a pivot table, based on the option of creating a Pivot Table template and using SAS to export out the new data, and then to refresh the file.

The first two steps are not shown here and are something you'd need to design on your own.

For updating the pivot table there are comments in the code, but I can expand on them I suppose:

/* Tell SAS to open Excel */

options noxwait noxsync;

data _null_;

     rc=system('start excel'); *Open Excel;

     rc=sleep(10);

run;

/* Execute VBA code via SAS DDE - open file, select sheet with pivot, put cursor on pivot table and refresh pivot table */

filename cmds dde 'excel|system';

data _null_;

     file cmds;

     put "[open(""\\&path\&f_name..xlsx"")]";  *Open a specific file;

     put '[workbook.activate("Pivot")]'; *Navigate to the sheet called Pivot;

     put '[select("R1C1")]'; *Select the first cell A1, you reference cells as Row#Column# in DDE;

     put '[pivot.refresh()]'; *Refresh the pivot table source;

     put "[save()]"; *Save the excel file;

     put "[quit()]"; *Quit Excel;

run;

quit;

Ask a Question
Discussion stats
  • 5 replies
  • 1847 views
  • 0 likes
  • 4 in conversation