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.
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;
Please post some example datasets (in data steps) with which we can test code, and the code you tried.
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 .
Hi,
I am not getting what is the issue with Code ?
I meant "what is the code you run when it fails, and what is the data against which you run that code?"
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.
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.
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.
Please post the code you use for reporting that dataset.
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;
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.
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.
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.
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".
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.