Data visualization with SAS programming

SAS EG Reporting(excel) issue

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

SAS EG Reporting(excel) issue

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

Attachment
Attachment
Attachment

Accepted Solutions
Solution
‎03-08-2015 01:35 PM
Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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


All Replies
Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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;

Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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

Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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?

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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.

Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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

Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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?

Solution
‎03-08-2015 01:35 PM
Grand Advisor
Posts: 17,323

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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.

Frequent Contributor
Posts: 107

Re: SAS EG Reporting(excel) issue

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 820 views
  • 0 likes
  • 2 in conversation