BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
draroda
Fluorite | Level 6

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

17 REPLIES 17
draroda
Fluorite | Level 6
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 .

draroda
Fluorite | Level 6

Hi,

 

I am not getting what is the issue with Code ?

 

 

draroda
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

draroda
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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;
draroda
Fluorite | Level 6

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.

 

 

Kurt_Bremser
Super User

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.

draroda
Fluorite | Level 6

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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