Hi:
My example exactly mimics your first posting, as shown below:
Your second posting example has these issues that I have a hard time figuring out because there's no context for the data or the desired output:
1) data shown is screen shot, data structure is not explained and can't be used with only partial code provided
2) data is not explained. Is it a CSV file? Is it a SAS dataset? Why are there ODS ESCAPECHAR strings in the data?
3) there is no color coding in the example that is supposed to be Excel output. The 3rd picture doesn't look like Excel output. There are not any cell boundaries and no column headers.
It is possible to do color coding with ODS EXCEL. I'm not sure that if you have multiple strings in a cell that you can do 2 different backgrounds in one cell but you CAN have 2 different font colors, as shown below:
One thing I like to do is to remove SAS from the picture and see whether something is possible in native Excel. So for example, in this worksheet that I typed into Excel, I can change the entire background for one cell, no matter how many text strings are in the cell:
However, look at the cell B3, where I have highlighted just the word "number" in the cell, the button to change the background becomes grayed out and there's not a way to change the background color of just one piece of the string. I can still change the font color of the highlighted word, as shown below:
So if Excel won't let you change the background of just one piece of a text string, SAS won't be able to do it either.
Cynthia
option validvarname=upcase;
data scores;
input Name$ Test_1$ Test_2$ Test_3$;
datalines;
Bill stomach hepatic bacteria
Carlos . allery rashes
Monique stomach pain vomit
chris heart . .
jay . hepatic virus
. . heart hyp
;
run;
data t1;
length name test_1 test_2 test_3 $50 header cell1 cell2 cell3 $200;
set scores;
if name=" " then name="[Missing]";
if test_1=" " then test_1="[Missing]";
if test_2=" " then test_2="[Missing]";
if test_3=" " then test_3="[Missing]";
if test_1= "heart" then phase="Phase 1";
else if test_1= "stomach" then phase="Phase 2";
else phase="Phase 3";
/*using ^n as escapechar to read in the proc report each variable in separate line inside the excel sheet cell feel */
/*free to change*/
header=strip(phase)||" "||strip(test_1);
cell1=strip(name)||","||"^n"||" "||strip(test_1)||","||"^n"||" "||strip(test_3);
cell2=strip(name)||","||"^n"||" "||strip(test_2)||","||"^n"||" "||strip(test_3);
cell3=strip(name)||","||"^n"||" "||strip(test_1)||","||"^n"||" "||strip(test_2);
run;
I created a dummy dataset for you . This data will similar to my dataset and follows same programming method. I used ^n during concatenation because I only know that way. If you have any other ideas please share. I am also attaching the excel output image. I am looking to produce the out put that looks like in the image. Yellow color highlight is for the cells that containing the word "[Missing]". If any typo errors please ignore,
ods ESCAPECHAR = '^';
ods excel file='C:\Users\xxx\Desktop\New folder\xxx\exxx_c.xlsx' options(sheet_name='xxx'
sheet_interval='none');
/* title1 j=c '#byvar(hline)';*/
%macro xx(ph=,phx=);
PROC REPORT data=sdsp5 nowindows
style(report)=[font=("Times New Roman", 11pt) protectspecialchars=on]
style(header)=[font=("Times New Roman", 11pt, bold) just=center protectspecialchars=on]
style(column)=[font=("Times New Roman", 11pt) protectspecialchars=on];
/* by ph ;*/
where &ph;
COLUMN ph Hline Study_Identifier brief_title Study_Design Study_status Study_start_date
Exchange_standards terminology_standards h1 i1 j1 k1 l1 m1 n1 o1;
DEFINE ph/ " " group noprint style=[just=c vjust=t cellwidth=2.745cm tagattr='type:String format:Text']; /*ph sorting order for study identifier*/
DEFINE Hline/ " " group noprint style=[font=("Times New Roman",11pt)just=c vjust=t vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
/*hline is for horizontal heading for the group like below excel"phase 2 stomach"
%if &phx=1 %then %do; /*This line I created macro because I want to display main heading only once at the top like
phase, name test1 test 2 in the sample excel pic. this line will make run the first group *//
/* below are the columns to display in cells, h1-o1 are the flags created if sting has "[Missing]"
DEFINE Study_Identifier/"Study Identifier" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
DEFINE brief_title/ "Brief Title" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
DEFINE Study_Design/ "Study Design" display style=[just=c vjust=t cellwidth=5.882cm tagattr='type:String format:Text'];
DEFINE Study_status /" Study Status" display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
DEFINE Study_start_date /"Study Start Date" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
DEFINE Exchange_standards /"Exchange standards" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
DEFINE terminology_standards /"Terminology Standards" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
DEFINE h1/ display noprint;
DEFINE i1/ display noprint;
DEFINE j1/ display noprint;
DEFINE k1/ display noprint;
DEFINE l1/ display noprint;
DEFINE m1/ display noprint;
DEFINE n1/ display noprint;
DEFINE o1/ display noprint;
%end;
%else %do;
DEFINE Hline/ " " group noprint style=[font=("Times New Roman",11pt)just=c vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
DEFINE Study_Identifier/"" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
DEFINE brief_title/ "" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
DEFINE Study_Design/ "" display style=[just=c vjust=t cellwidth=8.882cm tagattr='type:String format:Text'];
DEFINE Study_status /" " display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
DEFINE Study_start_date /"" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
DEFINE Exchange_standards /"" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
DEFINE terminology_standards /"" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
DEFINE h1/ display noprint;
DEFINE i1/ display noprint;
DEFINE j1/ display noprint;
DEFINE k1/ display noprint;
DEFINE l1/ display noprint;
DEFINE m1/ display noprint;
DEFINE n1/ display noprint;
DEFINE o1/ display noprint;
%end;
compute before hline/ style=[font=("Times New Roman", 11pt, bold) ];
line @140 hline $100.;
IF i1="Y" then do; call define ("hline","style","style={background=yellow}");/* I tried for one only to check but not succesful*/
end;
endcomp;
RUN;
%mend;
%xx(ph =%str(ph=1),phx=1);
%xx(ph =%str(ph>1));
ods excel CLOSE;
ods listing close;Thanks Cynthia. I understand it clearly now. Thanks for taking your time to explain. If it possible for you can you please check my Proc report code and tell me if any thing that cause errors. However I am still facing issue with displaying the color, I able to do flags in my final dataset before proc report and I can able to see the flags in the report if I ran without "noprint"option. I believe some thing in my proc report code making issue.
Hi:
You should be able to tell whether there are errors. What do you see in the SAS Log?
Your comment says that you tried something but it was not successful. However, you clearly have color coding in the cells of the table. Did your program produce that color coding? That looks successful to me.
If you also want color-coding in the compute before block which is the cell that says Phase 1, Phase 2 and Phase 3 I'm not sure you can do that with flags the way you are trying to do it. And, that requirement wasn't part of what you originally asked for. It looks to me like you are getting the color coding you wanted in the data cells. Is this all your code? It seems to me there should be more code, but with your code complicated by macro programming it's hard to tell. And I'm not sure you need macro program to suppress the headers the way you envision. For example. consider this simple example using a small subset of SASHELP.PRDSALE:
Taking all macro out of the picture and taking highlighting and color coding out of the picture, let's look at how PROC REPORT works when you use NOPRINT and the LINE statement to control the separation of groups. Notice that the headers are NOT repeated for the groups. Your macro seems to be trying to re-create the default PROC REPORT behavior when you have break processing on a GROUP or ORDER item using a COMPUTE BEFORE.
This user group paper might help you understand break processing and COMPUTE blocks better: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf
Also techniques like @140 are LISTING only options and are ignored by ODS RTF, ODS PDF, ODS HTML, ODS EXCEL, etc. There's not the ability to use column pointers with ODS in the same way there could be with LISTING (monospace) output.
Hope this helps. At some point, you might want to consider opening a track with Tech Support. They could look at all of your code and all of your data (including the program that you use to set the variable values for the color coding flags) and they might have some suggestions.
If I were going to make any suggestions, I'd suggest getting everything to work with one subset without using any macro programming and making sure that you got what you wanted without using macro coding. I'm not convinced that you even need macro programming. But since you don't show this new data, with the new flags, nobody could run your code. And the other issue is that when I try to paste your code into an editor, it appears that you have a comment that is not ended because in my editor, most of the program looks like a comment (in my editor, comments are green):
So that would be the first thing I'd recommend fixing. The next thing would be to "unmacroize" and simplify your program and make sure it works correctly without using macro programming. And, make sure that you even need macro logic to build your PROC REPORT because I don't see that it is necessary.
Hope this helps,
Cynthia
ods ESCAPECHAR = '^';
ods excel file='C:\Users\xx\Desktop\New folder\xx\rxx.xlsx' options(sheet_name='xx'
sheet_interval='none');
PROC REPORT data=sdsp5 nowindows
style(report)=[font=("Times New Roman", 11pt) protectspecialchars=on]
style(header)=[font=("Times New Roman", 11pt, bold) just=center protectspecialchars=on]
style(column)=[font=("Times New Roman", 11pt) protectspecialchars=on];
/*Ph variable created for sorting order purpose so that phase 1 comes first then phase 2 etc*/
COLUMN ph Hline xx yy zz kk mm nn cc;
DEFINE ph/ " " group noprint style=[just=c vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
DEFINE Hline/ order style=[font=("Times New Roman",11pt)just=c vjust=t vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
DEFINE Study_Identifier/"XX" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
DEFINE brief_title/ "yy" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
DEFINE Study_Design/ "zz" display style=[just=c vjust=t cellwidth=5.882cm tagattr='type:String format:Text'];
DEFINE Study_status /"kk"display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
DEFINE Study_start_date /"mm" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
DEFINE Exchange_standards /"nn" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
DEFINE terminology_standards /"cc" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
compute before hline/ style={just=c fontweight=bold};
line hline lenght $50.;
endcomp;
run;
ods excel close;
ods listing close;I did what you suggested , but I am unable to achieve the horizontal heading , please guide me where I am doing wrong in code, I am also providing my excel output. If you see the I just did not given the option "norprint" for "hline" variable to show in Excel file, so that I can explain it better. In the excel file Column A has "hline "vairable and row 2 has the value "Phase1" , row 4-"phase 2" and row 6- phase 3. What I am expecting is like to achieve how you achieved the countries you provided. I looking to get the "hline" values in horizontal headers just line one you provided. Ex: Phase1 header is above the below row 1, phase 2 is above row4, phase4 above 6 just like yours. Please help me out on this. Thank you
Hi: This is almost exactly like the SASHELP.PRDSALE example I posted previously. My guess is that something about your PH variable is preventing break processing from working. You don't show what the PH variable values are (your GROUP/NOPRINT) -- we only see what the HLINE variable is.
If you notice in my example, COUNTRY is the very first item on the COLUMN statement and I am doing a COMPUTE BEFORE COUNTRY. In your example, PH is the first item on the COLUMN statement and you are doing a COMPUTE BEFORE HLINE, which is the 2nd item on the COLUMN statement.
Since you do not seem to want to post any data in readable form, no one can work with your code. Look again at my example with SASHELP.PRDSALE, where I have COUNTRY, you should have HLINE. I have zero idea what your PH variable represents or why it's even on your report. But, my example works with COUNTRY as the first item.
Also, your code doesn't make sense. The variables on the COLUMN statement do NOT match the variables in the DEFINE statements.Here's your code highlighted where it doesn't make sense. Green items are matches between the COLUMN statement and the DEFINE statements. Pink highlights are the items listed on the COLUMN statement, but do not appear on DEFINE statements (which can be OK if the variables exist in the data and you want to take all the defaults). Yellow highlights are variables on the DEFINE statements, but not in the COLUMN statement -- which is not OK with PROC REPORT:
The variables on the COLUMN statement should be either variables in your data or they are COMPUTED items or statistic names. Otherwise you get warnings. Now, you might actually have items named xx yy zz kk mm nn cc in your data, but nobody knows whether you do because you don't post your data or any sample data that corresponds to your code.
Here's a simple example that mimics your code technique using SASHELP.CLASS. SASHELP.CLASS has 5 variables: NAME, SEX, AGE, HEIGHT, WEIGHT. So when I use your SAME technique, I generate WARNINGS and the output only shows NAME from SASHELP.CLASS:
But the bottom line is that what you show in your screen shot as what you want is exactly like my SASHELP.PRDSALE example. But the code you've posted is nothing equivalent to the code I posted. There's the issue of the variables on the COLUMN not matching the variables on the DEFINE and then theres the issue of HLINE not being the first item on the COLUMN statement .
Cynthia
Hi:
You are using the COMPUTE BEFORE and the CALL DEFINE on the wrong variables. There are no variables on the break line written with the LINE statement. Did you review my paper on break processing that I posted: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf -- the LINE statement writes EXTRA break lines, but the row from the LINE statement spans the entire table. There are NOT any data cells when you use a LINE statement.
So here are my numbered comments that correspond to the output below -- Your original code produced this output with all the color coding attempts removed. I made flag visible so you could see that it did not exist on the row written by the LINE statement:
1) in your CALL DEFINE, you are changing the color for the PRODUCT cell. Your LINE statement is writing out REGION, but you're changing the color of the PRODUCT cell. The LINE statement is writing 1 report row -- PRODUCT is NOT a column on this report row. FLAG is not a column on this report row. You can't color code PRODUCT on this row and you can't test FLAG on this row. At the point in time where the COMPUTE block before region is EXECUTING, the FLAG variable is not visible on the report row.
2) FLAG appears on every report row for data so you could have a COMPUTE block for FLAG that changed the background color of PRODUCT on each data row..
3) you spelled yellow incorrectly as yelllow, so the color will not be recognized when it does work.
Here's a reworked version of your program showing color coding that works on every data row and also shows how to color the LINE statement rows.
Notice that I have a separate COMPUTE block for FLAG and I test that value on every row where FLAG appears. Then I use the value of flag to determine the background color of the PRODUCT cell on that same row.
Just because EAST and WEST are underneath the PRODUCT column header, does not mean that you can use the CALL DEFINE in the COMPUTE BEFORE REGION to change the color of EAST or WEST. Notice that in the COMPUTE BEFORE REGION statement, I have added background= to the style override on the COMPUTE statement. The ONLY thing that touches the LINE output row is the style override that you have on the COMPUTE block (but there are NOT any data cells on that row, there's only what you write with the LINE statement).
Cynthia
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
2845 views
-
9 likes
-
-
