topic proc report across a variable but output another variable's value in the cell in Statistical Procedures

proc report across a variable but output another variable's value in the cell

gsk — Fri, 06 Mar 2020 19:08:20 GMT

Hi, I have some data that looks something like this:

data have1;
input ID question$ subcat$ response$;
datalines;
1 Q1 a Y
1 Q1 b Y
1 Q1 c Y
1 Q2 a Y
1 Q2 b Y
1 Q2 c N
1 Q2 d N
1 Q2 e Y
2 Q1 a N
2 Q1 b Y
2 Q1 c Y
2 Q2 a N
2 Q2 b Y
2 Q2 c N
2 Q2 d N
2 Q2 e Y
;
run;

I need the data to look like this:

	Q1	Q2
ID	a	b	c	a	b	c	d	e
1	Y	Y	Y	Y	Y	N	N	Y
2	N	Y	Y	N	Y	N	N	Y

How do we make the response variable output to the cell?

Re: proc report across a variable but output another variable's value in the cell

PaigeMiller — Fri, 06 Mar 2020 19:23:24 GMT

I don't think you can have character values Y and N underneath across variables, unless you make them numeric and formatted to produce Y and N.

data have1;
input ID question$ subcat$ response$;
if response='Y' then numeric_Response=1;
else numeric_response=0;
datalines;
1 Q1 a Y
1 Q1 b Y
1 Q1 c Y
1 Q2 a Y
1 Q2 b Y
1 Q2 c N
1 Q2 d N
1 Q2 e Y
2 Q1 a N
2 Q1 b Y
2 Q1 c Y
2 Q2 a N
2 Q2 b Y
2 Q2 c N
2 Q2 d N
2 Q2 e Y
;

proc format;
    value nf 1='Y' 0='N';
run;

proc report data=have1 nocompletecols;
    columns ("ID" id) question,subcat,numeric_response;
    define id/group ' ';
    define question/across;
    define subcat/across;
    define numeric_response/sum ' ' format=nf.;
run;

Re: proc report across a variable but output another variable's value in the cell

Ksharp — Sat, 07 Mar 2020 06:48:39 GMT

How about this one ?

data have1;
input ID question $ subcat $ response $;
datalines;
1 Q1 a Y
1 Q1 b Y
1 Q1 c Y
1 Q2 a Y
1 Q2 b Y
1 Q2 c N
1 Q2 d N
1 Q2 e Y
2 Q1 a N
2 Q1 b Y
2 Q1 c Y
2 Q2 a N
2 Q2 b Y
2 Q2 c N
2 Q2 d N
2 Q2 e Y
;
proc report data=have1 nowd ;
    columns ('ID' id) question,subcat,response;
    define id/group ' ';
    define question/across ' ' nozero;
    define subcat/across ' ' nozero;
    define response/group ' ' nozero ;
run;