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:\¤t_YEAR.Q¤t_quarter.\TEST_¤t_year._test.CSV",'")');
put script;
put 'OldBook.Sheets("A151DIN_¤t_year.").Cells.Select';
put 'OldBook.Sheets("A151DIN_¤t_year.").Selection.Copy';
put 'OldBook.Sheets("A151DIN_¤t_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!
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
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..
@soontobeexpert wrote:
Doing my best to learn ODS here..
This is not ODS.
I also attempted to use the filename fileout approach referenced here
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.