DATA Step, Macro, Functions and more

Transpose based on a group

Reply
Frequent Contributor
Posts: 85

Transpose based on a group

Hi,

I have a dataset which looks like this

NameCity
JohnWashington
JohnChicago
JohnNewyork
MaryWashington
MaryDallas

And I want to Transpose it based on Group of Name and City output should be something like this

Output_Report
John
Washington
Chicago
Newyork
Mary
Washington
Dallas

Is it possible to create a dataset/PDF report like this ?

Any help is really appreciated

Super User
Super User
Posts: 7,392

Re: Transpose based on a group

Question is, why would you want to, its then hard to see what is a name and what is a city.  If you have to then:

data want;

     set have;

     by name;

     if first.name then do;    

          output_report=name;

          output;

          output_report=city;

          output;

     end;

     else do;

          output_report=city;

          output;

     end;

run;

However to my mind it makes more sense to report both columns, but just group the first in a report:

proc report data=have;

     columns name city;

     define name / order "Name";

     define city / "City";

run;

This will display:

Name     City

John       Washington

               Chicago

               NewYork

Mary       Washington

               ...

Super User
Posts: 9,662

Re: Transpose based on a group

You really should post it at ODS and Report forum if it is a proc report problem.

data have;
input  Name $     City : $20.;
cards;
John     Washington
John     Chicago
John     Newyork
Mary     Washington
Mary     Dallas
;
run;
 
proc report data=have nowd;
     columns name city;
     define name / order "Name" noprint;
     define city / "City";
      compute before name;
       line name $;
      endcomp;
run;


Xia Keshan

Ask a Question
Discussion stats
  • 2 replies
  • 184 views
  • 0 likes
  • 3 in conversation