BookmarkSubscribeRSS Feed
archana
Fluorite | Level 6

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.

7 REPLIES 7
ballardw
Super User

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

stat_sas
Ammonite | Level 13

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

Reeza
Super User

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

archana
Fluorite | Level 6

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.

Reeza
Super User

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;

jacqelynl49
Calcite | Level 5

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;

 

Patrick
Opal | Level 21

@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/

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4790 views
  • 1 like
  • 6 in conversation