topic Re: Automating Excel pivot table in SAS in SAS Programming

Automating Excel pivot table in SAS

archana — Tue, 27 May 2014 16:36:12 GMT

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.

Re: Automating Excel pivot table in SAS

ballardw — Tue, 27 May 2014 16:41:24 GMT

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

Re: Automating Excel pivot table in SAS

stat_sas — Tue, 27 May 2014 17:01:14 GMT

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

Re: Automating Excel pivot table in SAS

Reeza — Tue, 27 May 2014 17:06:15 GMT

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

Re: Automating Excel pivot table in SAS

archana — Tue, 27 May 2014 17:45:54 GMT

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.

Re: Automating Excel pivot table in SAS

Reeza — Tue, 27 May 2014 19:06:06 GMT

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;

Re: Automating Excel pivot table in SAS

jacqelynl49 — Fri, 23 Feb 2018 20:32:46 GMT

I know this is a very old thread but it pertains to exactly what my question is today.

I have one tab with data and a summary tab with multiple pivot tables all pointing to that data. I have this all automated using an excel template. I need to distribute the file with all the pivot tables already updated. The problem is this piece of the code:

put '[pivot.refresh()]';

The first data step works for all pivot tables in column 1. Is there a way to refresh ALL?

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 '[worksheet.activate("Summary")]';

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

put '[pivot.refresh()]';

put "[save()]";

put "[quit()]";

run;

Re: Automating Excel pivot table in SAS

Patrick — Sat, 24 Feb 2018 01:03:34 GMT

@jacqelynl49

Please don't post new questions into old threads but ask a new question and reference the old thread.

As for using DDE: I strongly advice against it as you're creating legacy which can cause significant migration problems/costs when moving to a modern SAS architecture. https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/