SAS DDE Pivottables

Reply
Contributor
Posts: 21

SAS DDE Pivottables

Hallo,

In my SAS program I export data to Excel and refresh a Pivottable. This still works. I also try to show the details for a specific week number. I know that there is VBA code to do this. But for my report I try to avoid using VBA. So I tried the following:

filename out dde "Excel|I:\Reporting\Täglich\Test\[Sales_Report_2013_07_11.xlsx]Input!Z2S1:z75000s70" notab LRECL=5000;
 
data _null_;
set work.sales;
FORMAT SUM_PREM: numx.2;
dlm='09'x;
file out;
put (_all_) ('09'x);
run;

data _null_;
file ddecmd;
put '[pivot.refresh("PivotTable1")]';
put '[pivot.PivotTables("PivotTable1").PivotFields("CREATED_WEEK").PivotItems("01").ShowDetail = "True"]';
run;

Is there a way to do this? I hope someone could help me.

Christoph

Super Contributor
Posts: 644

Re: SAS DDE Pivottables

I would do as much of  the calculation in SAS as possible rather than trying to use remote control in Excel.

There is a week function in SAS (from 9.1) which is described in 22127 - Determine the week of the year from a SAS date value.  SAS has additional parameters to allow you control of whether you use Sunday or Monday as the first day of the week, and specifies explicitly when week 1 starts.  Just add a line to your data _Null_  code:

Created_Week = week(<saledate?>, <descriptor>) ;

Richard

Contributor
Posts: 21

Re: SAS DDE Pivottables

Hi Richard,

The calculation of the week number is still done. What I am trying to do is to save the report where the week of the current date is expanded. Like the following example.

week.JPG

Christoph

Super Contributor
Posts: 644

Re: SAS DDE Pivottables

So if I understand you correctly you can create year, month, week and day of week for all dates but only want to display the daily in your pivot in the final week.  And you want to control this, including the pivot update from SAS.  My DDE is very rusty so if I were forced into this situation I would try and keep that part as simple as possible.  So my suggestion is to create a separate column, null for each week but the last, and the last containing the day of week name and day of month, eg in your case "Mo 08".  The challenge is detecting which is the last week in the series, maybe use

     IF week(date) = week(today ()) THEN ....

Richard

Contributor
Posts: 21

Re: SAS DDE Pivottables

When I created this report I was asked to assure that after updating the report just the actual week is expanded. So it is more convient.

Till now we update the report out of another Excel sheet with a macro. To display the actual week I use this code:

WB.Sheets("Sales Report").PivotTables("PivotTable1").PivotFields("CREATED_WEEK").PivotItems(weekNr_act).ShowDetail = True

In my new version of the report I want to avoid VBA und include the report in my SAS batch job. After it is already possible to refresh a pivot table I thought there should be a way to expand and collapse columns.

Christoph

Ask a Question
Discussion stats
  • 4 replies
  • 450 views
  • 0 likes
  • 2 in conversation