topic Re: Wrong type of format for data type in ODS and Base Reporting

Wrong type of format for data type

draroda — Fri, 15 Jun 2018 09:43:32 GMT

Hi All,

I am trying to apply formatting to spanned header rows with the help of format but not able to do so and i am getting warning of Wrong type of format for data type.

The strange thing is that its working fine with SASHELP.class dataset but not working with my original data.

When i place only one dataset in tagset excelxp the things are working fine but my requirement is to have 22 reports in same excelsheet.

As sample code working fine and my required code not working , can you guide how can i move ahead.

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 10:07:28 GMT

Please post some example datasets (in data steps) with which we can test code, and the code you tried.

Re: Wrong type of format for data type

RW9 — Fri, 15 Jun 2018 10:09:48 GMT

What can we say, no test data, no code, no required output, no log, nothing. All we have here is:

Wrong type of format

So, my response is, correct the format. I don't see how you would "apply a format" to a spanned header, spanned headers in proc report - if indeed that is what you ues - forms:

columns("Some text to put across values" <variable> <variable>);
define <variable> / "Label for the variable" format=...;

Re: Wrong type of format for data type

draroda — Fri, 15 Jun 2018 10:27:13 GMT

proc format;
value $color
'Class' ='lightyellow'
;

run;
ods Tagsets.ExcelXP style=sasweb
file='"Desktop\Table.xml' 
options(orientation='landscape' sheet_name="Index" embedded_titles='yes'  );
title justify=left "Additional Tables – Data Cut &dcut" ;
proc report data=sashelp.class split='*' headskip spanrows spacing=0 nowd style(header)=[fontweight=bold background=$color. ];
columns ("Class" age sex height);
define age/display  order=data style(column)=[cellwidth=100 just=left font_weight=bold] "age" 
                  style(header)=[just=center]  ;
define sex/display  order=data style(column)=[cellwidth=800 just=left ] "sex" 
                  style(header)=[just=center]  ;
define height/display  order=data style(column)=[cellwidth=800 just=left ] "height" 
                  style(header)=[just=center]  ;
run;
ods Tagsets.ExcelXP close;

This is the code which i have used for my data.the issue is that it is working fine for this one .

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 10:36:21 GMT

And what are you doing when it fails?

Re: Wrong type of format for data type

draroda — Fri, 15 Jun 2018 10:57:41 GMT

Hi,

I am not getting what is the issue with Code ?

Re: Wrong type of format for data type

RW9 — Fri, 15 Jun 2018 11:02:09 GMT

I would hazard a guess that this:

[fontweight=bold background=$color. ];

Is where your issue lies. The format $color. is not applied to any actual data here, thus it looks incorrect to me.

When you put style items on the proc report line they apply to the whole proc report, hence have nothing really to do with the data.

Again, without any idea of what you are trying to achieve its hard to say, but that background statement would likely need to be associated with a variable which has character information like "Class".

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 11:05:38 GMT

I meant "what is the code you run when it fails, and what is the data against which you run that code?"

Re: Wrong type of format for data type

draroda — Fri, 15 Jun 2018 11:19:34 GMT

There is no issue in it as it is working on SASHELP.CLASS data.

I have taken it from here.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Colouring-spaning-column-headers-in-different-colour-with-PROC/td-p/35759

Re: Wrong type of format for data type

draroda — Fri, 15 Jun 2018 11:20:57 GMT

There are 20 variables and 5400 rows where i am running the code.

Like sashelp.class , dataset has character variables and numeric variables.

Not sure why proc report not highlighting spanned rows for this one only.

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 11:27:33 GMT

Since the WARNING is most probably coming from the structure of the data and not its contents, just posting a data step with all the columns and, say, 3 observations, along with the code used, would be sufficient for diagnosing and testing.

Re: Wrong type of format for data type

draroda — Fri, 15 Jun 2018 11:47:16 GMT

Hi,

Here is the data.

data test;
infile datalines truncover;
input subject dose cohort $ 9-16  day grade aerel $ 22-33 aeser $ 34-37 teae $ 38-41 ;
datalines;
100 0.3 Cohort1 34 3 Not related Yes Yes 
101 0.2 Cohort4 98 4 Related     No  Yes
102 0.7 Cohort6 32 1             Yes No 
;

Spanned rows starting from variable day to teae where there is issue.

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 12:11:06 GMT

Please post the code you use for reporting that dataset.

Re: Wrong type of format for data type

RW9 — Fri, 15 Jun 2018 12:30:44 GMT

And did you try putting Other in the format declaration as given in that post you refer me to, I don't see it in the code you post here and they specifically talk about that error and provide a possible workaround.

Re: Wrong type of format for data type

Kurt_Bremser — Fri, 15 Jun 2018 12:40:27 GMT

I made up this code, it produces a yellow background and no WARNING:

proc format;
value $color
'Numbers' = 'lightyellow'
;
run;

data test;
infile datalines truncover;
input subject dose cohort $ 9-16  day grade aerel $ 22-33 aeser $ 34-37 teae $ 38-41 ;
datalines;
100 0.3 Cohort1 34 3 Not related Yes Yes 
101 0.2 Cohort4 98 4 Related     No  Yes
102 0.7 Cohort6 32 1             Yes No 
;
run;

ods Tagsets.ExcelXP style=sasweb
file='$HOME/sascommunity/table.xml' 
options(orientation='landscape' sheet_name="Index" embedded_titles='yes'  );

proc report data=test split='*' headskip spanrows spacing=0 nowd style(header)=[fontweight=bold background=$color. ];
columns (subject dose cohort ("Numbers" day grade aerel aeser teae));
define subject/display  order=data style(column)=[cellwidth=100 just=left font_weight=bold] "Subject" 
                  style(header)=[just=center]  ;
define dose/display  order=data style(column)=[cellwidth=100 just=left ] "Dose" 
                  style(header)=[just=center]  ;
define cohort/display  order=data style(column)=[cellwidth=100 just=left ] "Cohort" 
                  style(header)=[just=center]  ;
define day/display  order=data style(column)=[cellwidth=100 just=left ] "Day" 
                  style(header)=[just=center]  ;
define grade/display  order=data style(column)=[cellwidth=100 just=left ] "Grade" 
                  style(header)=[just=center]  ;
define aerel/display  order=data style(column)=[cellwidth=100 just=left ] "aerel" 
                  style(header)=[just=center]  ;
define aeser/display  order=data style(column)=[cellwidth=100 just=left ] "aeser" 
                  style(header)=[just=center]  ;
define teae/display  order=data style(column)=[cellwidth=100 just=left ] "teae" 
                  style(header)=[just=center]  ;
run;
ods Tagsets.ExcelXP close;

Re: Wrong type of format for data type

draroda — Mon, 18 Jun 2018 04:37:16 GMT

Thanks a lot.

Code works for me as well.

The issue occurs only when i keep all datasets in same tagset.I tried my code only for one dataset and in that case it is working absolutely fine , but when i include other datasets, at that time issue arises.

Re: Wrong type of format for data type

Kurt_Bremser — Mon, 18 Jun 2018 06:00:03 GMT

This is going nowhere. Post ALL relevant datasets and ALL relevant code, so we can diagnose it. Don't make us pry the information out of your nose bit by bit.

Re: Wrong type of format for data type

draroda — Mon, 18 Jun 2018 06:11:29 GMT

Thanks a lot for your help on this issue.

But as it is not working , i am revering my code to old one and leave the formatting stuffs beside the task.