- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I was afraid of that. Yes, this works, but I didn't want to have to define formats. Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;