BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
purpleclothlady
Pyrite | Level 9

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.

 

purpleclothlady_0-1651263102913.png

 

thank you for helping.

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
purpleclothlady
Pyrite | Level 9
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
Diamond | Level 26

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.

--
Paige Miller
purpleclothlady
Pyrite | Level 9

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
SAS Super FREQ
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
Pyrite | Level 9
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
Super User Tom
Super User

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
Pyrite | Level 9

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 10 replies
  • 628 views
  • 1 like
  • 4 in conversation