Build reports by using ODS to create HTML, PDF, RTF, Excel, text reports and more!

Wrong type of format for data type

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Wrong type of format for data type

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.

 

 

 


Accepted Solutions
Solution
‎06-18-2018 02:11 AM
Super User
Posts: 10,623

Re: Wrong type of format for data type

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,623

Re: Wrong type of format for data type

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser
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 .

Super User
Posts: 10,623

Re: Wrong type of format for data type

And what are you doing when it fails?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser

Hi,

 

I am not getting what is the issue with Code ?

 

 

Super User
Posts: 10,623

Re: Wrong type of format for data type

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser

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.

Super User
Posts: 10,623

Re: Wrong type of format for data type

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser

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.

Super User
Posts: 10,623

Re: Wrong type of format for data type

Please post the code you use for reporting that dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎06-18-2018 02:11 AM
Super User
Posts: 10,623

Re: Wrong type of format for data type

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser

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.

 

 

Super User
Posts: 10,623

Re: Wrong type of format for data type

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Wrong type of format for data type

Posted in reply to KurtBremser

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.

Super User
Super User
Posts: 9,866

Re: Wrong type of format for data type

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

☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 317 views
  • 0 likes
  • 3 in conversation