DATA Step, Macro, Functions and more

Organize edit data table

Occasional Contributor
Posts: 5

Organize edit data table



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

 LocationMethodVar 1Var 2
Person 1x113
Person 1y157
Person 1x224
Person 1y268
Person 2x1911
Person 2y11315
Person 2z11719
Person 2x21012
Person 2y21416
Person 2z21820





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


  Var 1Var 2
 LocationMethod 1Method 2Method 1Method 2
Person 1x1234
Person 1y5678
Person 2x9101112
Person 2y13141516
Person 2z17181920


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,



Super User
Posts: 6,785

Re: Organize edit data table

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;


Occasional Contributor
Posts: 5

Re: Organize edit data table

Posted in reply to Astounding

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)? 




Frequent Contributor
Posts: 132

Re: Organize edit data table

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;
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

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


data want;
merge Var1_1
by Person Location;
Frequent Contributor
Posts: 77

Re: Organize edit data table

[ Edited ]

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;

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

Ask a Question
Discussion stats
  • 4 replies
  • 4 in conversation