How to prevent the excel pivot fields from resetting after I transfer data from SAS

[SaketChaudhari]

Hi everyone.

I am trying to automate a part of my job, which involves copying the data from the xlsx SAS output, manually, and then pasting it into a excel sheet which has all the charts already made.

The charts are pivot charts, which have there source data in some sheet which goes by the name raw_data.

I have used some dde job to transfer the data into required cells of raw_data, so that it doesn't ruin the formatting of the cells in the raw_data.

This allows pivot table to automatically recognise the change in data, and once I refresh it, the new data is there.

But all the fields from the pivot tables are removed for some reason. Could anyone please tell me if there is a way to prevent this automatic removal of the fields. I am attaching relevant information here.

/*Creating temporary data set to explain*/

data comp_new;
input month$ Product$ Sob_Category$ _2021_06 _2021_07 _2021_08 _2021_09 _2021_10 _2021_11 _2021_12;
datalines;
2021_11 Saket Loss 8 8 8 8 8 8 8
2021_12 Saket Win 9 9 9 9 9 9 9
;
run;
/* Trying the DDE method*/
options noxwait noxsync;
x ' "P:\User\SChaudhari\Automation\QC_Comparision\New_Excel\QC_2021_12.xlsx" ';
filename example1 dde 'Excel|[QC_2021_12.xlsx]Raw_Data!r2c1:r3c10' notab;

data _null_;
x=sleep(10);
run;
data _null_;
set comp_new;
file example1;

put month '09'x Product '09'x Sob_Category '09'x _2021_06 '09'x _2021_07 '09'x _2021_08 '09'x _2021_09 '09'x _2021_10 '09'x _2021_11 '09'x _2021_12;
run;

Here are the pictures to show how the excel pivot is changing.

Before:

  

After:

Note that the first two lines have changed successfully.

But the fields of the pivot tables have been reset.

Please let me know if there is more information needed from my end.

Also, please let me know if I can bug someone continually as I will face more troubles as I move forward with dde. I am open to paying for it (Please adjust as per the Indian standards).

Thanks and Kind Regards

[ballardw]

DDE is notoriously not robust. Microsoft quit suggesting that as a solution years ago. Also there are other applications that use the communication channels that DDE tries to use and may prevent DDE from communicating at all or possibly corrupting things and it may take a lot of time to find out what is going on.

I suggest writing to a completely different XLSX file and addressing values to specific cells until you can control things as wanted.

[SaketChaudhari]

That is impossible for me as the ods output of SAS can't match the malleability of excel. I don't want to go into the nitty gritty of creating slicers via SAS, or repositioning charts and slicers via SAS. Also, reporting connections of multiple pivot tables via SAS.

So, I think this is going to be the better approach, I am almost done too.
Just need to figure out a way to prevent forced refresh of pivot tables, and then refresh in a manner that will allow the same fields as before.

[ballardw]

Have you checked that the DDE transferred "raw data" is coming into the spread sheet as numeric? Since you are doing calculations with the pivot I don't know if values written as character would work.

I actually can't test DDE code because one of the communications applications in my office uses the DDE links and nothing will get written and can't tell characteristics from pictures.

I'm not sure what you mean by the "malleability of Excel" in this context but I hate making graphs in Excel. Mostly because I learned that in SAS before Excel would such and having created hundreds of maps with about 35 lines of SAS code after the data was prepared was ever so much quicker. Plus I never had to worry about someone "fudging" the exported data to "adjust" one or more of the graphs to mean something not supported by the data.

[SaketChaudhari]

The data being transferred is part numeric and part character.

The character values will help the fields of the pivot table, and the numeric data will be the values corresponding to those fields.

I did find a walkaround.

Basically, SAS was hard refreshing the pivot tables, which makes excel forget the cache data of those pivot tables. (Cache data such as which fields to retain after refreshing).
So I wrote an excel macro, which will retain the pivot cache, and then refresh the pivot tables. I will call the xlsm macro in sas. It works, and everything happens well.

But alas, now I had to replace the xlsx with the xlsm format.

And when I copy the xlsm into the new month data folder, and open it, it shows that the file is locked for editting.

So caught in two ways here, will love to just use xlsx and not make xlsm, or, use xlsm without having to manually copy it from previous month to new month, and then refreshing the pivot using the new month's data.

Please let me know if any of this needs further explaination.