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
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!