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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.