topic Re: SAS EG Reporting(excel) issue in Graphics Programming

SAS EG Reporting(excel) issue

KiranMaddi — Sat, 07 Mar 2015 23:13:32 GMT

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

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 11:13:03 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 11:45:38 GMT

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;

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 14:54:33 GMT

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

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 15:05:31 GMT

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

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 16:32:40 GMT

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?

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 16:38:40 GMT

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.

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 16:42:29 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 17:02:39 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 17:03:56 GMT

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

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 17:16:47 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 17:24:52 GMT

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?

Re: SAS EG Reporting(excel) issue

Reeza — Sun, 08 Mar 2015 17:35:36 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Sun, 08 Mar 2015 17:40:19 GMT

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.

Re: SAS EG Reporting(excel) issue

KiranMaddi — Mon, 09 Mar 2015 12:56:23 GMT

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.