ODS Copy and Paste Values - Excel

[soontobeexpert]

Hey SAS family,

I am trying to use SAS to open an excel file, select all the values on a sheet, copy them, paste them right back to where they were as values, save, and then close.

This is a work around to a problem I am having using the sas export function with rather complex labels. I keep getting double quotes around alphanumeric values, and instead of attempting to recode a bunch of the macro variables I have, this will be infinitely easier. My experience with ODS and scripting in excel is pretty much nil, so any help or starting point would be greatly appreciated!

Heres what I`ve scrapped together from a previous step, by my attempts at modify it to make it work didnt pay off.

data _null_;

length script filevar $256;

script = catx('\',pathname('WORK'),'PasteIt.vbs');

filevar = script;

script="'"||'cscript "'||trim(script)||'"'||"'";

call symput('script',script);

file dummy1 filevar=filevar recfm=v lrecl=512;

put 'Dim objExcel';

put 'Dim OldBook';

put 'set objExcel = CreateObject("Excel.Application")';

script=catt('Set OldBook=objExcel.Workbooks.Open("',"P:\&current_YEAR.Q&current_quarter.\TEST_&current_year._test.CSV",'")');

put script;

put 'OldBook.Sheets("A151DIN_&current_year.").Cells.Select';

put 'OldBook.Sheets("A151DIN_&current_year.").Selection.Copy';

put 'OldBook.Sheets("A151DIN_&current_year.").Selection.PasteSpecial Paste:=xlPasteValues';

put 'OldBook.Save';

put 'OldBook.Close';

put 'objExcel.DisplayAlerts = True';

put 'objExcel.Quit';

run;

data _null_;

call system(&script.);

run;

Thanks!

[Reeza · Accepted solution]

I'm not sure this approach could be considered easier than others. Can you explain your problem with exporting 'complex labels' some more?

Otherwise, search for the Rich Poor Proc Export and you can find custom written VBA code and a SAS macro that some users have developed to deal with these types of issues.

[soontobeexpert]

Thanks for the reply.

Some of the labels of the table(s) I am trying to export to excel are macro variables, and some start with numbers (ex 2017Q1).

My initial approach was attempting to use proc export and using the label function to maintain the labels when it went over to excel. On this surface this worked fine, however, the next system processsing these files needs CSVs as inputs, and the labels exported using this method are contained in double quotes, so are not picked up correctly.

I also attempted to use the filename fileout approach referenced here

https://communities.sas.com/t5/SAS-Enterprise-Guide/Exporting-text-field-to-txt-file-without-quote-q...

but wasnt able to figure out how to make it work with multiple columns of macro variables. For example, one of my column headings is =%substr("current_year.",4,2)'Q1'; Without rewriting the logic of the program, I couldnt figure out how to make these type of headings work with the approached referenced above.

In my head, a simple script that opens the final excel file, copies it, pastes it as a value, saves and closes should be the easiest (albeit not the fastest) approach. Any help constucting such a script would be much appreciated!

Doing my best to learn ODS here..  

[Reeza]

---

@soontobeexpert wrote:

Doing my best to learn ODS here..  

---

This is not ODS. 

---

I also attempted to use the filename fileout approach referenced here

https://communities.sas.com/t5/SAS-Enterprise-Guide/Exporting-text-field-to-txt-file-without-quote-q...

but wasnt able to figure out how to make it work with multiple columns of macro variables. For example, one of my column headings is =%substr("current_year.",4,2)'Q1'; Without rewriting the logic of the program, I couldnt figure out how to make these type of headings work with the approached referenced above

---

This would the correct approach, one PUT statement for your headers which can have whatever format you want. 

And one PUT statement for your data. I would consider revisting this approach, posting what you've tried and what didn't work. Use a representative sample including your macro variables.