topic chnage structure of table by row and columns points in SAS Programming

chnage structure of table by row and columns points

Ronein — Wed, 11 Mar 2020 10:54:53 GMT

Hello

I have a summary table that contain 3 categorical variables and one continuous variable(called "Reduce").

There are 18 rows (and 18 numbers of "Reduce" var).

The target is to display same table in a structure of 3 rows and 6 columns.

I have added 2 variables called:Row Column that tell uf where to locate each number.

What is the way to create the required table please?

Data Summarytbl;
infile datalines dsd;
input Cat2_Y $ 	Cat1_Y $ 	Cat1_X $ 	Reduce 	Row  	Column ;
cards;
No,SecondMax,No,15000,1,1
No,SecondMax,10,14500,1,2
No,SecondMax,20,12000,1,3
No,Max,No,11500,1,4
Yes,SecondMax,No,11000,2,1
No,Max,10,10500,1,5
Yes,SecondMax,10,10000,2,2
No,Max,20,9500,1,6
Yes,SecondMax,20,9000,2,3
Yes,Max,No,8500,2,4
Yes,Max,10,8000,2,5
Yes,Max,20,5000,2,6
Yes,SecondMax,No,4500,3,1
Yes,SecondMax,10,4000,3,2
Yes,Max,No,3500,3,4
Yes,SecondMax,20,3000,3,3
Yes,Max,10,2500,3,5
Yes,Max,20,2000,3,6
;
run;

Re: chnage structure of table by row and columns points

PaigeMiller — Wed, 11 Mar 2020 11:37:01 GMT

What text should we have the row names be? 1 2 or 3

What text should we have the column names be? 1 2 3 4 5 or 6

Re: chnage structure of table by row and columns points

Cynthia_sas — Wed, 11 Mar 2020 13:36:54 GMT

Hi:

This is what you can get from PROC REPORT using your ROW, COLUMN and other variables:

However, I'm not sure from your description that this is what you intended with Cat2_Y, Cat1_Y, Cat1_X and Reduce all under the columns. And this output generates a report, not a data set.

Cynthia

Re: chnage structure of table by row and columns points

RichardDeVen — Sun, 15 Mar 2020 23:56:05 GMT

Reshaping your data into a 3 x 6 table will lose the categorical context maintained by cat1_x cat1_y cat2_y.

Transpose will create an output data set with the new shape

proc sort data=have;
  by row column;
run;

proc transpose data=have out=want;
  by row;
  id column;
  var reduce;
run;

Tabulate can report the data in a new shape

proc tabulate data=have;
  class row column;
  var reduce;

  table 
    row='', 
    column=''*reduce=''*sum=''
  ;
run;