beginner merge help

[nursienurse]

Can someone tell me what I'm doing wrong here?

data one;

input persons $ city $ rank;

datalines;

twoperson dc 29

twoperson la 14

twoperson nyc 17 ;

run;

data two;

input persons $ city $ rank;

datalines;

threeperson dc 32

threeperson la 13

threeperson nyc 64 ;

run;

proc sort data=one;

by city;

proc sort data=two;

by city;

run;

data combined;

merge one two;

by city;

run;

proc print data=combined;

run;

Right now it's just reprinting data two.

I need it to look like this:

       twoperson     threeperson 

dc        29                     32

la           14                     13

nyc        17                      64

so what am I doing wrong SAS board?

any and all help much appreciated!

[PaigeMiller]

Merge does exactly what you see it doing.

In order to turn the rows into columns named twoperson and threeperson, you would have to run PROC TRANSPOSE on your data sets one and two (which turns rows into columns), and then merge.

Also, you don't want a semicolon at the end of your "nyc" datalines.

[arodriguez]

Hi nursienurse,

Merge statement join two datasets by a common variable.

Right now, you are joining by city, so you are creating a dataset with variables persons, city and rank, but mixing info of two datasets.

Try to define dataset two with an other column name, something like 

data two;
input persons2 $ city $ rank2;
datalines;
threeperson dc 32
threeperson la 13
threeperson nyc 64 ;
run;

(Take care of names are persons2, rank2 now)

So when you merge now, you should optain something like

       persons       persons2       rank                  rank2    

dc    twoperson  threeperson   29                     32

la     twoperson  threeperson   14                     13

nyc  twoperson  threeperson   17                      64

[MikeZdeb · Accepted solution]

Hi, try ...

data three (drop=persons);
merge one (rename=(rank=twoperson)) two (rename=(rank=threeperson));
by city;
run;

Obs    city    twoperson    threeperson

 1     dc          29            32
 2     la          14            13
 3     nyc         17            64