07-12-2013 05:13 AM
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;
FORMAT SUM_PREM: numx.2;
put (_all_) ('09'x);
put '[pivot.PivotTables("PivotTable1").PivotFields("CREATED_WEEK").PivotItems("01").ShowDetail = "True"]';
Is there a way to do this? I hope someone could help me.
07-13-2013 12:07 AM
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>) ;
07-15-2013 02:13 AM
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.
07-15-2013 09:27 AM
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 ....
07-15-2013 09:58 AM
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.