BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

Hello SAS experts, need a bit of help. I am trying to run a macro that generates reports in excel. Having running the same code with two different macro variables. Why only one is producing reports with graphs and cover page and the other is not(just printing the data as it is in excel).Below you can find the difference in the output that is before the code sends them to excel. Please help me out. please find the two reports  attached.

Note: I see no code in the macros that creates the graphs and cover page.


                                         #  Name                          Type    Type

                                        1  'Agg Graphs$'                 DATA    TABLE   

                                        2  'Agg Graphs$'Print_Area       DATA    TABLE   

                                        3  'DI Graphs$'                  DATA    TABLE   

                                        4  'DI Graphs$'Print_Area        DATA    TABLE   

                                        5  'Direct Internet$'            DATA    TABLE   

                                        6  'Direct Internet$'Print_Area  DATA    TABLE   

                                        7  'Price Change List$'          DATA    TABLE   

                                        8  'Tele Graphs$'                DATA    TABLE   

                                        9  'Tele Graphs$'Print_Area      DATA    TABLE   

                                       10  A                             DATA    TABLE   

                                       11  Aggregator$                   DATA    SYSTEM TABLE

                                       12  Aggregator$Print_Area         DATA    TABLE   

                                       13  Changes                       DATA    TABLE   

                                       14  Contents$                     DATA    SYSTEM TABLE

                                       15  Contents$Print_Area           DATA    TABLE   

                                       16  Cover$                        DATA    SYSTEM TABLE

                                       17  Cover$Print_Area              DATA    TABLE   

                                       18  Flag                          DATA    TABLE   

                                       19  In                            DATA    TABLE   

                                       20  TSn                           DATA    TABLE   

                                       21  Telesales$                    DATA    SYSTEM TABLE

                                       22  Telesales$Print_Area          DATA    TABLE   

                                       23  Tn                            DATA


                                                    1  A     DATA    TABLE   

                                                    2  A$    DATA    SYSTEM TABLE

                                                    3  Bn    DATA    TABLE   

                                                    4  Bn$   DATA    SYSTEM TABLE

                                                    5  IFn   DATA    TABLE   

                                                    6  IFn$  DATA    SYSTEM TABLE

                                                    7  INn   DATA    TABLE   

                                                    8  INn$  DATA    SYSTEM TABLE

                                                    9  TFn   DATA    TABLE   

                                                   10  TFn$  DATA    SYSTEM TABLE

                                                   11  TNn   DATA    TABLE   

                                                   12  TNn$  DATA    SYSTEM TABLE

                                                   13  TSn   DATA    TABLE   

                                                   14  TSn$  DATA    SYSTEM TABLE

                                                   15  Tn    DATA    TABLE   

                                                   16  Tn$   DATA    SYSTEM TABLE


Accepted Solutions
Super User

Exactly. Those are in the workbook already. They're named ranges in the Excel workbook. Since the Halifax one doesnt have the precreated workbook none of those are listed and its why the proc datasets is empty for Halifax - nothing to report.

View solution in original post

Super User

You need to show the code that generates the reports, and most especially the log in a situation like this.

The information above looks to be the result of a proc datasets call that doesn't have the nodetails and/or nolist option specified.

Obsidian | Level 7

Hi Reeza,

Thanks for your reply. I am running a macro that is nearly two pages. This is just a part of the code. I have also attached full code and log for both Halifax(Graphs and cover pages not generated) and BIS(Graphs and cover page is generated) in case if you want to see it.

Once again thank you very much. I have been struggling with this since two days.your help will be very much appreciated.

    Proc Transpose Data = Cmb_results_&Channel Out = Export_&Channel;

       Id Date;

       Var Misc_IsAccept_n

























Libname Myexcel pcfiles path="&output.\New Business Car\Monthly Pricing Pack - &Name..xls" Scan_Text = No;

Proc Datasets Library = myexcel;

       Delete &Channel.n "&Channel.n.$"n;


    quit ;

    Data myexcel.&Channel.n ;

       Set work.export_&Channel ;

       Run ;

    Libname Myexcel clear;

Super User

The log from the run that didnt work? With MPRINT/SYMBOLGEN on?

Obsidian | Level 7

Yes, MPRINT and SYMBOLGEN are on for both of them.

Super User

The export appears to have worked fine to me. I can see the data between the two worksheets and they look different, but perhaps not what you want. Have you verified that each table has the correct data before the export occurs?

Obsidian | Level 7

Yes, I have checked the data. In case of halifax the data on its own exported to excel where as for BIS data long with graphs,cover,contents etc.

Super User

Is it only Halifax that's not working? Your Halifax sheet appears to be only the exported sheets, whereas the BIS appears to export to a pre-created Excel file. My guess is the pre-created file doesn't exist for Halifax.

Obsidian | Level 7

It is only working for BIS(%MI(PC,NBS,BIS,BIS,BIS)) and B&B(%MI(PC,NBS,B&B,BBG,BBG)). Rest of them same as halifax.

Obsidian | Level 7

Where do you see that it is pre-created for BIS?

Super User

I can't see per se, but I'm 99% certain.

It has linked graphs and formatted information that isn't/can't be generated from SAS, the data is only exported to those 5/6 sheets.

My guess, which you should know by the way, is that this code is supposed to export data to a pre-formatted excel workbook, that will then update all the graphs/tables so your report is generated. Its a fairly quick method of generating reports from SAS to Excel.

Obsidian | Level 7

You may be right Reeza. But, One thing is still confusing.If you see my actual post above. Why there are some tables in SAS EG(in a library called myexcel) called DI Graphs$'Print_Area, Agg Graphs$'Print_Area  etc for BIS unlike Halifax?

Super User

Exactly. Those are in the workbook already. They're named ranges in the Excel workbook. Since the Halifax one doesnt have the precreated workbook none of those are listed and its why the proc datasets is empty for Halifax - nothing to report.

Obsidian | Level 7

Right I got you. I will keep that in mind and tomorrow will try to find out if the excel is not created for those that are not working. I will keep you updated. Thanks a ton for your help. you are a star.

Obsidian | Level 7

Hi Reeza, what you said is exactly right in a way. The templates are missing for those brands. I could not have done it with out your inputs. Thanks a lot for all your help. Keep doing what you are doing. All the best.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2 in conversation