topic Re: beginner merge help in SAS Programming

beginner merge help

nursienurse — Tue, 20 Oct 2015 13:21:38 GMT

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!

Re: beginner merge help

PaigeMiller — Tue, 20 Oct 2015 13:38:19 GMT

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.

Re: beginner merge help

arodriguez — Tue, 20 Oct 2015 13:40:44 GMT

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

Re: beginner merge help

MikeZdeb — Tue, 20 Oct 2015 15:20:09 GMT

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