BookmarkSubscribeRSS Feed
AdrianGriffin
Obsidian | Level 7

I'm trying to place some SAS-genreated sparklines in a multiple-sheet workbook. But the macro fails when inserting graphs in multiple sheets. Here's a small example. 

 

The code works when all the sheets are set to tom, but fails when trying to put graphs in dick and harry.

 

data graphs;
  input File :$30. Sheet :$8. Cell :$4.;
  length String $60;
  *sheet='Tom';
  string='c:\users\agriffin\g\' !! trim(file) !! '#' !! trim(sheet) !! '!' !! cell;
cards;
g8EPM2.5010.png Tom      b3
g8EPM2.5020.png Tom      b4
g8EPM2.5030.png Dick     d7
g8EPM2.5099.png Dick     d8
run;

proc sql noprint;
  select string into :placement separated by ', ' from graphs;
quit;
%put &placement;
%excel_enhance(open_workbook=c:\users\agriffin\desktop\test_book.xlsx, insert_image=%quote(&placement), file_format=xlsx);


g8EPM2.5010.pngg8EPM2.5020.pngg8EPM2.5030.pngg8EPM2.5199.png
9 REPLIES 9
Cynthia_sas
SAS Super FREQ
Hi:
I'm not sure HOW you are trying to insert the graphics. Are you using ODS? If so, are you using ODS HTML, ODS TAGSETS.EXCELXP or ODS EXCEL inside your %excel_enhance macro program? Or, are you using DDE? Or??? The answer to your question is inside your macro program when you invoke it and whether there are any error messages in the log. This might be something to address with Tech Support, since they could look at the code INSIDE the macro program to try to help you figure out what's going wrong.

cynthia
AdrianGriffin
Obsidian | Level 7
Cynthia:
It's Chevell's macro, included in the SGF2016_SAS177.ZIP file that I downloaded from your ftp site.
Cynthia_sas
SAS Super FREQ
Ah, does it use TAGSETS.TABLEEDITOR? I forgot that as an option? Still if you're not getting error messages in the log probably better to open a track with Tech Support.

cynthia
Reeza
Super User

In your macro call you're not specifying the cell, is that intentional? Otherwise how do you specify the multiple destinations?

Since the macro is demo'd with multiple sheets this should be possible.

AdrianGriffin
Obsidian | Level 7
The cell is specified in the insert_image argument. If you run the code and do a %put &placement, you'll see how the cell and sheet is specified.
ballardw
Super User

@AdrianGriffin wrote:
The cell is specified in the insert_image argument. If you run the code and do a %put &placement, you'll see how the cell and sheet is specified.

We don't have your data.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

You can run the macro with the options MPRINT and SYMBOLGEN to see details of the code generated.

You could post the log in a code box opened with the forum {i} icon.

 

 

AdrianGriffin
Obsidian | Level 7
The excel sheet is just an empty sheet that the macro puts the graphs into. All the workings are in the SAS code that I pasted into my original message. If you don't want to download the spreadsheet, just create an empty workbook with three tabs --tom, dick, and harry.
Reeza
Super User

Ok, different question - did the demo from the paper work as expected for you?

AdrianGriffin
Obsidian | Level 7
I didn't try Chevell's example, but my code works as expected when putting all of the graphs in the same sheet. If you uncomment the sheet=tom line, everything will work.

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
  • 9 replies
  • 1422 views
  • 0 likes
  • 4 in conversation