BookmarkSubscribeRSS Feed
soontobeexpert
Fluorite | Level 6

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;

 

  

7 REPLIES 7
Shmuel
Garnet | Level 18

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?

 

 

soontobeexpert
Fluorite | Level 6

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.

 

 

Reeza
Super User

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.

soontobeexpert
Fluorite | Level 6

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... 

Reeza
Super User

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. 

 

Shmuel
Garnet | Level 18

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 */

 

 

soontobeexpert
Fluorite | Level 6

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.     

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3058 views
  • 0 likes
  • 3 in conversation