SAS EG Reporting(excel) issue

[KiranMaddi]

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

[Reeza]

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.

[KiranMaddi]

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

        Misc_IsAccept_sum

        Quote_VehicleNetPremium_mean

    Quote_VehicleCommission_Mean

        Sold_VehicleCommission_mean

        Sold_VehicleNetPremium_mean

        Phone_sum

           S_FinanceFee_sum

        Sold_FinanceFee_mean

        S_Legal_sum

        Sold_Legal_mean

        S_Breakdown_sum

        Sold_Breakdown_Mean

        S_GRC_sum

        Sold_GRC_mean

        S_KeyCare_sum

        Sold_KeyCare_mean

        S_CarbonOffsetting_sum

        Sold_CarbonOffsetting_mean

        S_CarbonOffsetProject_sum

        Sold_CarbonOffsetProject_mean

    S_ExcessProtection_sum

           Sold_ExcessProtection_mean

        Sold_IncomePerPolicy_mean;

       Run;

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;

       Run;

    quit ;

    Data myexcel.&Channel.n ;

       Set work.export_&Channel ;

       Run ;

    Libname Myexcel clear;

[Reeza]

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

[KiranMaddi]

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

[Reeza]

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?

[KiranMaddi]

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.

[Reeza]

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.

[KiranMaddi]

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.

[KiranMaddi]

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

[Reeza]

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.

[KiranMaddi]

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?

[Reeza · Accepted solution]

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.

[KiranMaddi]

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.

[KiranMaddi]

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.