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
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;
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)?
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.