BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KiranMaddi
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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
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

14 REPLIES 14
Reeza
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.

KiranMaddi
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

        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
Super User

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

KiranMaddi
Obsidian | Level 7

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

Reeza
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?

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

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

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

KiranMaddi
Obsidian | Level 7

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

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

KiranMaddi
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?

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

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

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

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
  • 14 replies
  • 2186 views
  • 0 likes
  • 2 in conversation