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!
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
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.