color block in data step/proc report

[purpleclothlady]

Hi all:

I have the below dataset, and plan to output by subject and visit (want) using ODS report, how to do that in DataStep or Proc report ?

Thank you,

data have;
input subject $7. visit $20.;
datalines;
102-001 Visit 1
102-001 Visit 1
102-001 Visit 2
121-002 Visit 1
121-002 Visit 1
121-002 Visit 2
124-003 Visit 2
124-003 Visit 2
124-003 Visit 2
125-002 visit 3
125-002 visit 3
125-002 visit 3
128-002 Visit 2
128-002 Visit 2
128-002 Visit 2
128-002 visit 3
;run;
proc sort;by subject visit;run;

/***want***/
102-001 Visit 1 white
102-001 Visit 1 white
102-001 Visit 2
121-002 Visit 1 white
121-002 Visit 1 white
121-002 Visit 2
124-003 Visit 2 white
124-003 Visit 2 white
124-003 Visit 2 white
125-002 visit 3
125-002 visit 3
125-002 visit 3
128-002 Visit 2 white
128-002 Visit 2 white
128-002 Visit 2 white
128-002 visit 3

[PaigeMiller]

There is no ODS REPORT.

Do you mean PROC REPORT and ODS ____________ ?

Also, can you please explain the logic here, and why you are typing "white" next to some rows and not other rows? Spend some time, give us a complete and clear explanation of the problem and the desired output, instead of the extremely brief explanation you gave.

[purpleclothlady]

Hi Paige:

The goal is to color code by subject and visit. the final output look like this when I output as excel , please see attachment or screen shot.

thank you for helping.

[PaigeMiller]

Thanks. I don't see an answer to my earlier request:

please explain the logic here, and why you are typing "white" next to some rows and not other rows? Spend some time, give us a complete and clear explanation of the problem and the desired output, instead of the extremely brief explanation you gave.

[purpleclothlady]

Hi Paige:
Please ignore column "white" I just want to give an example of look of the final dataset.
the goal is to output the dataset to excel and highlight cells by subject and visit, which I sent as a screen shot. Hopefully this explains the goal clearly.

thanks,
purpleclothlady

[PaigeMiller]

We're not communicating. I don't think you understand. I still don't see an explanation of the logic that determines which cell gets colored and which don't. I have asked twice for this logic, I don't see it. I have asked twice for "Spend some time, give us a complete and clear explanation of the problem and the desired output, instead of the extremely brief explanation you gave" and I don't see it.

[purpleclothlady]

hi all:

data have;
input subject $7. visit $8.;
datalines;
102-001 Visit 1
102-001 Visit 1
102-001 Visit 2
121-002 Visit 1
121-002 Visit 1
121-002 Visit 2
124-003 Visit 2
124-003 Visit 2
124-003 Visit 2
125-002 visit 3
125-002 visit 3
125-002 visit 3
128-002 Visit 2
128-002 Visit 2
128-002 Visit 2
128-002 visit 3
;
run;
proc sort;by subject visit;run;

/*Goal: Need to create a new variable: COLORFLAG using dataset HAVE */
/*The final dataset WANT looks like this */

data want;
input subject $7. visit $8. colorflag $8.;
datalines;
102-001 Visit 1 white
102-001 Visit 1 white
102-001 Visit 2
121-002 Visit 1 white
121-002 Visit 1 white
121-002 Visit 2
124-003 Visit 2 white
124-003 Visit 2 white
124-003 Visit 2 white
125-002 visit 3
125-002 visit 3
125-002 visit 3
128-002 Visit 2 white
128-002 Visit 2 white
128-002 Visit 2 white
128-002 visit 3
;
run;

Thank you,

[Cynthia_sas]

Hi:
Although I see what you say you want, I do not understand the logic of how you need to create a value for your COLORFLAG variable. For example, some Visit 1 rows have the COLORFLAG of white, but then some Visit 2 rows are also white for COLORFLAG. None of the Visit 3 rows have white for COLORFLAG. Can a Visit 3 ever have a COLORFLAG value of white? What is the difference between the Visit 1 rows and the Visit 2 rows. Are there only ever 3 visits? Can you ever have a Visit 4 or Visit 5? My guess is that you want the first visit number for a subject to get the color white for COLORFLAG and then the next visit number has the COLORFLAG set to either nothing or some other color. But I can't write a program based on a guess. Because when I look at 125-002, they only have Visit3 and they do not have white for COLORFLAG, so I don't understand the logic of how you are assigning values to COLORFLAG.
Cynthia

[purpleclothlady]

Hi all:
Now I know the confusion came from.
Logic: Whenever SUBJECT OR VISIT change, the COLORFLAG will change. is this clear now? thank you 🙂
for example:
SUBJECT VISIT COLORFLAG LOGIC
102-001 Visit 1 white (starting value)
102-001 Visit 1 white ( no change-keep same color)
102-001 Visit 2 (change value of VISIT)
121-002 Visit 1 white (change value of subject and visit)
121-002 Visit 1 white (no change-keep same color)
121-002 Visit 2 (change value of visit)
124-003 Visit 2 white (change value of subject )
124-003 Visit 2 white (no change-keep same color)
124-003 Visit 2 white (no change-keep same color)
125-002 visit 3 (change value of subject and visit)
125-002 visit 3 (no change-keep same color)
125-002 visit 3 (no change-keep same color)
128-002 Visit 2 white (change value of subject and visit)
128-002 Visit 2 white (no change-keep same color)
128-002 Visit 2 white (no change-keep same color)
128-002 visit 3 (change value of visit)

[Tom · Accepted solution]

So use BY group processing to create your COLORFLAG variable.

It is probably easier to use binary variable, but with some extra IF/THEN logic you could create a text variable if you insist.

data want;
   set have;
   by subject visit;
   retain colorflag 0;
   colorflag=mod(colorflag+first.visit,2);
run;

[purpleclothlady]

Thanks everyone-Paige, Cynthia  for your help and time, you rock. this works !!