WARNING: col3 is not in the report definition

[Vigneswar]

Hi, 

I am trying to export the dataset example into Canada.xml under sheet "ALL_Items".

Below is the sample code I am using,

ods tagsets.ExcelXP file="/sasconfig/External/Canada.xml" style=meadow
options (frozen_headers='0' frozen_rowheaders='0' );
ods results on;

ods tagsets.ExcelXP options (sheet_name="All_Items");

proc report data= example
style(header)=[background=LightGray] ;

columns col1 col2 col3 col4 col5;
define col1 /display;
define col2 /display;
define col3 /display;
compute col3 ;
if col3 = '1_SUPER RED' then
call define ('col3','style','style=[foreground=Maroon
font_color=white]');
endcomp;
define col4 /display;
define col5 /display;
run;

In the output xml file, instead of getting the columns names it is showing the label of those columns.

Thus I am getting a warning in the log that 

WARNING: col3 is not in the report definition

So in the output xml file, I have the label as column name and without colors in the col3.

Any suggestions are highly appreciated.

Thanks

[Cynthia_sas · Accepted solution]

Hi:

  With the correct attributes, the code works for me. Perhaps you misunderstand the PROC REPORT defaults. If a variable has a label, PROC REPORT will use that label, by default. If you want to override the label, then you need to control for that.

  Also, I simplified your code a bit to remove the frozen headers -- just to have the most basic invocation.

  You did not provide test data. Not sure why you are setting foreground to maroon and then trying to set font_color to white. Foreground IS font color. Did you mean to set background to maroon? Anyway, you needed to use the correct style attribute names for the CALL DEFINE. This worked for me, after making some fake data from SASHELP.CLASS:

Cynthia

[Vigneswar]

Hi Cynthia,

Thanks for your reply. 

I was setting foreground to maroon for a reason as I have to differentiate variables in a column which has risk factor.

I have used the same attributes that has been used by you. 

Here the challenge was I had 39 columns and in one column I needed to differentiate the risk factor based on values.

Below is the column where I wanted to differentiate in colors,

define Risk_Assessment_Color /color=black;
compute Risk_Assessment_Color ;
if Risk Assessment Color = '1_SUPER RED' then
call define (_col_,'style','style=[background=Maroon color=white]');
endcomp;

But when I execute I am getting the below error, 

ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Any suggestion on this?

And also is it possible to get the names in the excel output instead of label?

Thanks again for your response.

[Cynthia_sas]

Hi:
1) you do not provide any data and I cannot replicate your error with my fake data
2) You do not show your FULL log, only the error messages and in debugging errors it is vital to see WHERE the problem is occurring. Usually, seeing the FULL log is most useful, not just a snippet with the error message.
3) The DEFINE statement that you do show with "/color=black" is NOT valid PROC REPORT syntax and is not anything I showed in my code. With most styles, the default font color in the data cells IS black, so this is unnecessary, anyway. But if you DID want to set the data cell color to black, the correct DEFINE statement to have a default of black would be:
define Risk_Assessment_Color / style(column)={color=black};

4) As I explained in my previous posting. PROC REPORT uses labels by default so your choice is to a) change the labels in the DEFINE statement or b) use the system option that suppresses the use of labels (I leave this for you to look up in the documentation).

I proved to you that it was possible to change the background and foreground colors in a data cell based on the values in the cell, in my posted example and output. I showed you how I made the data and I showed ALL of my code. I did not need to show my log because I did not have any errors in my code.

If your code generates errors, then nobody can help you if you don't provide the full context by showing exactly where in the log you had issues. If you can't post data, or your full code, then you are asking people to guess what your data looks like to to guess what your code looks like. So, to help everyone out, you should be able to replicate your problem on a subset of fake data that you can post with your full code that generates the error. If you can't replicate your problem when you use fake data, then there is something probably amiss with your real data. For example, if your string was in lower case, (1_super red), then the comparison would fail.

I don't know whether Risk_Assessment_Color is in your COLUMN statement because you didn't show your COLUMN statement, but your CALL DEFINE syntax looks correct, so there's either another place in your code with problems or in your data with problems.

Cynthia

[Vigneswar]

Hi,

Thanks for your response.

Below is my code,

ods tagsets.ExcelXP file="/sasconfig/Lev1/External/Canada_CM_ Jan_14_2019.xml" style=meadow
options (sheet_name="All_Items");

proc report data= work.canada_all_items
style(header)=[background=LightGray height=60 width=20 font_weight=Bold] ;

columns Business_Unit Category Brand SKU_Type Control_Model_Link Base_Material SKU Description Risk_Classification_Level
Risk_Assessment_Color Ultimate_Risk Disco_Risk_in_Dollars_at_Cull_Da Shopping_List Prod_Scheduled_in_Next_8_Weeks
Excess_Above_MIPO Demand_Comments Supply_Comments Commercial_Comments Discontinuation_Date Date_of_First_Shipment
External_Material_Group Planner Forecast_State Cases_of_Bad_Inventory Cases_of_Good_Inventory Bad_Inventory_Cost
Disco_Risk_in_Dollars_at_Cull_30 Disco_Risk_in_Cases_at_Cull_Da30 Disco_Risk_in_Cases_at_Cull_Date Expected_Dollars_in_WriteoffsNex
Cost_per_Case Weeks_over_MIPO Cases_with_30_days_until_St Cases_within_60_days_until_St Cases_within_90_days_until_St
Cases_within_120_days_until_St _26_Week_WriteOff Pkg_Risk Pkg_Link;

define Business_Unit /display;
define Category /display ;
define Brand /display ;
define SKU_Type /display ;

define Control_Model_Link /display ;
compute Control_Model_Link;
call define (_col_, 'URL', Control_Model_Link);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;

define Base_Material /display ;
define SKU /display ;
define Description /display ;
define Risk_Classification_Level /display ;

define Risk_Assessment_Color/display;
compute Risk_Assessment_Color ;
if Risk_Assessment_Color = '1_SUPER RED' then
call define (_col_,'style','style=[background=Maroon color=white]');
if Risk_Assessment_Color = '2_RED' then
call define (_col_,'style','style=[background=Red color=white]');
if Risk_Assessment_Color = '3_YELLOW' then
call define (_col_,'style','style=[background=Yellow color=black]');
if Risk_Assessment_Color = '4_GREEN' then
call define (_col_,'style','style=[background=Green color=white]');
endcomp;

define Ultimate_Risk /display;
define Disco_Risk_in_Dollars_at_Cull_Da /display;
define Shopping_List /display;
define Prod_Scheduled_in_Next_8_Weeks /display;
define Excess_Above_MIPO /display;
define Demand_Comments /display;
define Supply_Comments /display;
define Commercial_Comments /display;
define Discontinuation_Date /display;
define Date_of_First_Shipment /display;
define External_Material_Group /display;
define Planner /display;
define Forecast_State /display;
define Cases_of_Bad_Inventory /display;
define Cases_of_Good_Inventory /display;
define Bad_Inventory_Cost /display;
define Disco_Risk_in_Dollars_at_Cull_30 /display;
define Disco_Risk_in_Cases_at_Cull_Da30 /display;
define Disco_Risk_in_Cases_at_Cull_Date /display;
define Expected_Dollars_in_WriteoffsNex /display;
define Cost_per_Case /display;
define Weeks_over_MIPO /display;
define Cases_with_30_days_until_St /display;
define Cases_within_60_days_until_St /display;
define Cases_within_90_days_until_St /display;
define Cases_within_120_days_until_St /display;
define _26_Week_WriteOff /display;
define Pkg_Risk /display;
define Pkg_Link /display;

run;
ods tagsets.ExcelXP close;

When I execute the code, I am getting my desired output in SAS and the .xml file is created.

But when I try to open the output file, I get the error "problems came up in the following areas during load".

Earlier I was able to open and now I couldn't. Please let me know if you need further informations.

Appreciate for all your earlier suggestions.

Thanks,

Vigneswar

[Vigneswar]

Hi Cynthia,

Further to my previous post I want to add few info.

My requirement is I have 3 dataset (X, Y, Z and I am trying to export them into the xml file "Canada_CM_ Jan_14_2019.xml" under three different sheets A, B, C.

When I export X into my xml file everything is fine. But now i want to export 3 datasets into same xml file "Canada_CM_ Jan_14_2019.xml" under 3 sheets.

Thanks in advance.

Vigneswar 

[Cynthia_sas]

Hi:

  This technique works for me to make 3 sheets:

As you can see, each sheet in the Excel file corresponds to a section inside one big TAGSETS.EXCELXP step.

 Posting your code was good. However, no one can run your code without making data. And since we don't know the structure of your data, or have any data to test with, any suggestions at this point would just be guesses.

  If you can't post any data you might want to consider opening a track with Tech Support. They can look at ALL of your code and ALL of your data and help you come to a resolution.

Cynthia

[Vigneswar]

Thanks a ton Cynthia for  allyour suggestions. It helped me so much indeed.

Only thing I am missing in my Output is I am not able to increase the header height in each sheets. Apart from that, everything looks fine!

Thanks again 🙂 

Vigneswar

[Cynthia_sas]

Hi:

  Typically, I don't try to over-control HEIGHT and WIDTH. And, I ALWAYS use a unit of measure. That is a best practice whether specifying font_sizes (always use 14pt, not just 14) or specifying height or width (I always use IN).

  I find that usually all I need to do is adjust the width -- changing the width of a cell (such as to .75in) will force the height to grow to accommodate the text in the cell.

In this example, notice how it is possible to alter the default width of all cells, but then, change the width for some of the cells. Notice that the width setting changed the height needed for the last 5 variables.

Hope this helps,

Cynthia