proc report to display values in data table

[cminard]

I have data in the following format:

data have;
input variable $ level $ mygroup $ measure $ response $;
cards;
Var1 L11 G1 M1 yes
Var1 L11 G1 M2 no
Var1 L11 G1 M3 maybe
Var1 L12 G1 M1 no
Var1 L12 G1 M2 no
Var1 L12 G1 M3 no
Var2 L21 G1 M1 yes
Var2 L21 G1 M2 yes
Var2 L21 G1 M3 yes
Var2 L22 G1 M1 yes
Var2 L22 G1 M2 yes
Var2 L22 G1 M3 maybe
Var1 L11 G2 M1 no
Var1 L11 G2 M2 yes
Var1 L11 G2 M3 yes
Var1 L12 G2 M1 yes
Var1 L12 G2 M2 yes
Var1 L12 G2 M3 maybe
Var2 L21 G2 M1 no
Var2 L21 G2 M2 no
Var2 L21 G2 M3 maybe
Var2 L22 G2 M1 yes
Var2 L22 G2 M2 no
Var2 L22 G2 M3 no
;
run;

I am trying to use proc report to display the "response" variable in an organized fashion.

What I want is:

		G1			G2
Variable	Level	M1	M2	M3	M1	M2	M3
Var1	L11	yes	no	maybe	no	no	no
	L12	yes	yes	yes	yes	yes	maybe
Var2	L21	no	yes	yes	yes	yes	maybe
	L22	no	no	maybe	yes	no	yes

I have tried using proc report, but it is not quite working.

proc report data=have missing;
column ('variable' variable) ('level' level) ('' mygroup), measure, ('' response);
define variable / '' group order=data;
define level / '' order order=data;
define mygroup / '' across order=data;
define measure / '' across order=data;
define response / '' display;
run;

That codes yields:

		G1			G2
		M1	M2	M3	M1	M2	M3
variable	level
Var1	L11	yes
			no
				maybe
					no
						yes
							yes
	L12	no
			no
				no
					yes
						yes
							maybe
Var2	L21	yes
			yes
				yes
					no
						no
							maybe
	L22	yes
			yes
				maybe
					yes
						no
							no

I don't have to use proc report, but that's the closest I've come to generating the table that I want. Any suggestions?

Thank you in advance.

[ballardw · Accepted solution]

Your proc report desired result shows Level as a Group variable, not order.

But to use that Response would want to have a statistic, which is pretty hard to do with character variables. So perhaps a minor change to the data:

proc format;
invalue ynm (upcase)
'YES'   = 1
'NO'    = 0
'MAYBE' = -1
;
value ynm
1 = 'Yes'
0 = 'No'
-1 ='Maybe' 
;
run;

data have;
   input variable $ level $ mygroup $ measure $ response ynm.;
   format response ynm.;
cards;
Var1 L11 G1 M1 yes
Var1 L11 G1 M2 no
Var1 L11 G1 M3 maybe
Var1 L12 G1 M1 no
Var1 L12 G1 M2 no
Var1 L12 G1 M3 no
Var2 L21 G1 M1 yes
Var2 L21 G1 M2 yes
Var2 L21 G1 M3 yes
Var2 L22 G1 M1 yes
Var2 L22 G1 M2 yes
Var2 L22 G1 M3 maybe
Var1 L11 G2 M1 no
Var1 L11 G2 M2 yes
Var1 L11 G2 M3 yes
Var1 L12 G2 M1 yes
Var1 L12 G2 M2 yes
Var1 L12 G2 M3 maybe
Var2 L21 G2 M1 no
Var2 L21 G2 M2 no
Var2 L21 G2 M3 maybe
Var2 L22 G2 M1 yes
Var2 L22 G2 M2 no
Var2 L22 G2 M3 no
;
run;

proc report data=have missing;
column ('variable' variable) ('level' level) ('' mygroup), measure, ('' response);
define variable / '' group order=data;
define level / '' group order=data;
define mygroup / '' across order=data;
define measure / '' across order=data;
define response / '' sum;
run;

Using a custom informat to read the values into a numeric value and then a custom format to display the numeric values with text.

[cminard]

I was afraid of that. Yes, this works, but I didn't want to have to define formats. Thank you!

[ballardw]

---

@cminard wrote:

I was afraid of that. Yes, this works, but I didn't want to have to define formats. Thank you!

---

I so hate to work with character values for "response" type items it is almost second nature for my to create a numeric value. That way I get to control order as well as check for valid value, i.e. Other= _error_ option on the invalue. That way if somehow a value "not sure" sneaks in then I get an invalid data message and can fix the values or the informat definition. Plus things like the way you wanted to create the output with Proc Report in a manner that requires a statistic.

Another option that would not require an informat/format pair might be the Report Writing Interface in the data step though learning the bits of code to do across values and row spanning aren't trivial and might require some data reshaping.

[Shmuel]

I hope next code will help you. For the example data it works:

data have;
input variable $ level $ mygroup $ measure $ response $;
cards;
Var1 L11 G1 M1 yes
Var1 L11 G1 M2 no
Var1 L11 G1 M3 maybe
Var1 L12 G1 M1 no
Var1 L12 G1 M2 no
Var1 L12 G1 M3 no
Var2 L21 G1 M1 yes
Var2 L21 G1 M2 yes
Var2 L21 G1 M3 yes
Var2 L22 G1 M1 yes
Var2 L22 G1 M2 yes
Var2 L22 G1 M3 maybe
Var1 L11 G2 M1 no
Var1 L11 G2 M2 yes
Var1 L11 G2 M3 yes
Var1 L12 G2 M1 yes
Var1 L12 G2 M2 yes
Var1 L12 G2 M3 maybe
Var2 L21 G2 M1 no
Var2 L21 G2 M2 no
Var2 L21 G2 M3 maybe
Var2 L22 G2 M1 yes
Var2 L22 G2 M2 no
Var2 L22 G2 M3 no
;
run;
proc sort data=have; by variable level mygroup; run;
data _null_;
 set have;
  by variable level mygroup;
  file print;
  if _N_=1 then do; /* titles */
     put @20 "G1"   @40 "G2" ;
     put @1 "Variable" @11 "Level"
         @20 "M1" @25 "M2" @30 "m3"
         @40 "M1" @45 "M2" @50 "m3"
      ;
  end;
  If first.variable then put @1 variable @;
  if first.level then put @11 level @;
  if mygroup = "G2" then indent=20; else indent=0;
  select (upcase(measure));
     when ("M1") put @20+indent response @;
     when ("M2") put @25+indent response @;
     when ("M3") put @30+indent response @;
     otherwise putlog measure=;
  end;
  if last.level then put;
run;