topic Using SAS with Excel - Open workbook, copy and past as values, save and close in SAS Programming

Using SAS with Excel - Open workbook, copy and past as values, save and close

soontobeexpert — Fri, 20 Oct 2017 13:20:25 GMT

Hello Everyone,

I have a unique problem and need some help. I need to create a SAS script that opens an excel workbook, selects everything, copies it, and then pastes it all as values, saves it, then closes it.

Fairly straight forward although I have limited experience working with Excel from SAS.

I wont get into the details too much, but we use this excel file to load another application, and some of my values (labels mostly) are coming out in Excel with " " quotes around them.

The simplest solution is to have a short script at the end that just copy and pastes the values into the same workbook.

Any help is greatly appreciated!

Below is a starting point, but does not do what I want. The code below opens a workbook, selects a specific tab of a work sheet, saves then closes. I need it to copy and paste values then save and close. Its a starting point anyway, but Im out my depth here... Thanks!

%macro Selecting_Excel_Sheet (Analyst);

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("',"&FilePath.\&Analyst. &Year.Q&Quarter. FFA.xlsx",'")');

put script;

put 'OldBook.Sheets("Income").Select';

put 'OldBook.Sheets("Investment").Select';

put 'objExcel.DisplayAlerts = False';

put 'OldBook.Save';

put 'OldBook.Close';

put 'objExcel.DisplayAlerts = True';

put 'objExcel.Quit';

run;

data _null_;

call system(&script.);

run;

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

Shmuel — Fri, 20 Oct 2017 13:53:18 GMT

SAS enables read excel worksheets into sas dataset (importing the data) or

write it into excel sheets (exporting).

It is not clear what do you mean by copy/paste the values ?

What kind of output should it be? A sas dataset or an excel worksheet ?

If the output is an execl sheet - how should output differ from input?

Importing data from excel - the column should be either a charcter type or numeric type.

Sas examines the values of a column, enables one or more rows to hold the label, then

according the other rows deine its type.

Dou you need the labels in the output? or you want to skip over?

Beyond, your macro misses the %MEND; line to close, and I did not check your macro details.

Why do you need a macro? What arguments you want to supply?

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

soontobeexpert — Fri, 20 Oct 2017 14:00:08 GMT

Hey Shmuel, thanks for the reply.

SAS can do a lot more than just import and export from and into Excel.

I need to write some code in SAS that opens an Excel workbook of my choosing, selects the entire range (sheet), copies the range, pastes the range as values (thus removing and " " quotes that exist in my data), saves the workbook and then closes.

You could liken what I am trying to do to an import then immediate re-export, but the data is already created... so importing a workbook, then re-exporting it wouldn't change anything; my data would still contain the " " values. Staying in Excel, and copying and pasting as values gets around this.

Yes, I need to maintain all labels, but the " " are not solely in the labels, hence why I'm trying to avoid any hardcoded label name approaches.

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

Reeza — Fri, 20 Oct 2017 15:06:38 GMT

I think this is a right tool for the right job situation.

You can do this in SAS, most likely using DDE though and that's outdated so I don't recommend that anymore...but it's definitely possible.

In this case, I suspect a VB macro is a better solution. I would also consider fixing it in a different way if possible. How are you creating the Excel file in the first place, how is the data getting there with quotes. I'd probably fix that process instead. Assuming your response is, that's not possible/out of my control here's one way:

http://analytics.ncsu.edu/sesug/2013/HOW-09.pdf

In general, search on Lexjansen.com for this and you'll find examples. You'll need to make sure you have X commands enabled, for example this won't work in SAS Studio or EG.

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

Shmuel — Fri, 20 Oct 2017 15:15:57 GMT

You can remove the " " by several ways:

1) new_var = compress(old_var,'"'); /* will remove all double auotes */

2) new_var = substr(old_var,2,length(old_var) - 1); /* will keep mid quotes */

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

soontobeexpert — Fri, 20 Oct 2017 15:34:14 GMT

Hey again, all valid answers I and I will definitely explore them! Thanks for all the input.

The example in my first question was not for this process; something entirely different. I just thought it would save some time in getting across what I was trying to achieve. I don't know how to write DDE mark-up to do what I am trying to do, was merely posting a similar example.

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

soontobeexpert — Fri, 20 Oct 2017 17:32:12 GMT

Trying to attack this problem I realized why I remember why I thought ODS might be best approach. Some of the labels I have begin with numbers; something SAS doesn't like ex. 17Q1.

Just some logic for the approach...

Re: Using SAS with Excel - Open workbook, copy and past as values, save and close

Reeza — Fri, 20 Oct 2017 17:39:30 GMT

OPTION VALIDVARNAME=ANY allows SAS to have names that start with numbers.

@soontobeexpert wrote:

Trying to attack this problem I realized why I remember why I thought ODS might be best approach. Some of the labels I have begin with numbers; something SAS doesn't like ex. 17Q1.

Just some logic for the approach...

ODS doesn't fit into this process that you've mentioned. If you created a file via ODS then its highly likely the issue can be fixed before you export the data.