Organize edit data table

[morten1]

Hi, 

I have got a dataset with information in the following format: 

	Location	Method	Var 1	Var 2
Person 1	x	1	1	3
Person 1	y	1	5	7
Person 1	x	2	2	4
Person 1	y	2	6	8
Person 2	x	1	9	11
Person 2	y	1	13	15
Person 2	z	1	17	19
Person 2	x	2	10	12
Person 2	y	2	14	16
Person 2	z	2	18	20

Is it possible to move some of the rows, so that I can compare the two methods in two adjacent columns, like this?

		Var 1	Var 2
	Location	Method 1	Method 2	Method 1	Method 2
Person 1	x	1	2	3	4
Person 1	y	5	6	7	8
Person 2	x	9	10	11	12
Person 2	y	13	14	15	16
Person 2	z	17	18	19	20

I don't think this is possible in excel, and I am new to SAS. Any suggestions? Or do I have to plot it manully?

Thank you,

Morten

[Astounding]

This is untested, but it should give you a quick-and-dirty version of what you asked for.

proc tabulate data=have;

   class person location method;

   var var1 var2;

   tables person * location, (var1 var2) * sum * method;

run;

[morten1]

Thank you, this works great. 

I get the results only in results viewer, and would like to have it as a SAS data set and as a new excel file. I have tried the ods html output as .xls file, but this gives me an excel sheet whit dots (instead of commas) and excel then automatically makes the values to dates. 

Is there a solution for:

1. Make a SAS data set from results in Results Viewer 

2. Get an excel sheet with the correct formatting (with commas, not converted as dates)? 

[dcruik]

Here's another way to do what you're looking for.  It's not dynamic, but you could probably develop some additional code to make it dynamic if needed (as far as additional variables and methods other than 2 each).  Hope this helps!

data have;
input Person$ Location$ Method$ Var1 Var2;
datalines;
Person1 X 1 1 3
Person1 Y 1 5 7
Person1 X 2 2 4
Person1 Y 2 6 8
Person2 X 1 9 11
Person2 Y 1 13 15
Person2 Z 1 17 19
Person2 X 2 10 12
Person2 Y 2 14 16
Person2 Z 2 18 20
;
run;

%macro transform(method,var);
data &var._&method (Rename=(&var=&var._Method&method) Keep=Person Location &var);
set have;
Where Method="&method";
run;
%mend;

%transform(1,Var1)
%transform(2,Var1)
%transform(1,Var2)
%transform(2,Var2);

data want;
merge Var1_1
      Var1_2
      Var2_1
      Var2_2;
by Person Location;
run;

[MadhuKorni]

proc report data =have
out = have1(drop = _break_ rename =(_c3_ = Method1Var1 _c5_ = Method2Var1 _c4_ = Method1Var2 _c6_ = Method2Var2));
columns Person Location Method,(Var1 Var2);
define Person/group ;
define Location /Group;
define Method/across;
run;

Data Want;
retain Person Location Method1Var1 Method2Var1 Method1Var2 Method2Var2;
set Have1;
run;