beginner merge help

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

beginner merge help

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!


Accepted Solutions
Solution
‎10-20-2015 12:09 PM
Valued Guide
Posts: 765

Re: beginner merge help

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,615

Re: beginner merge help

[ Edited ]

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.

Frequent Contributor
Posts: 144

Re: beginner merge help

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

Solution
‎10-20-2015 12:09 PM
Valued Guide
Posts: 765

Re: beginner merge help

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 268 views
  • 0 likes
  • 4 in conversation