How to create a new variable while using one-to-one merging

[sasnewbie5]

 I have two separate datasets that I want to merge into one new data set. One dataset has the variables "name" and "employee id". The other has variables "salary" and "ID". I need to combine the two data sets and only list the "name" and "salary" variables in the new data set. How would I do that? The code I have so far lists all of the variables in both datasets and I'm not quite sure how to specify only the ones that I want. 

*Of note, in the first dataset, it's called "EmpID" and in the second it's called "ID". 

Any and all help is welcomed! Thanks!

[novinosrin]

Since there is no example data and going with your question, it appears all you need is rename one of your id as the other id name and keep only the variables your want

[sasnewbie5]

I don't follow. The code I have is as follows:

/*******/

data Dept1;
set hw8.dept1names;
set hw8.dept1salaries;
run;

/******/

Since I haven't specified any variables, how would I rename it? I'd prefer not to adjust any of the actual dataset content.

Also, I have the two datasets that I'm referring to but I'm not sure if I can upload them to this thread or not. As you can probably tell, I'm very new to SAS and the SAS community.

[novinosrin]

Can you plz post a sample of your two datasets and your expected output. A small sample with 5 records or so will help. Thank you

Then you tell us your constraints/rules 

[sasnewbie5]

I have attached a screenshot of the two datasets to this thread.

The expected output is to use one-to-one merging to combine the two datasets and then list just the name and salary in the newly created dataset.

[novinosrin · Accepted solution]

data one;
input name $ empid $;
cards;
nelson 254
mitchell 362
perez 910
carter 935
;
data two;
input salary  id $;
cards;
103100 254
100200 362
73500 910
88400 935
;

data want;
merge one two;
keep name salary;
run;

NOTES:

1. The above assumes you really are after a one to one merge and the records in both datasets matches rather having to do an inner join

2. Both datasets have the same number of obs.  

[Reeza]

You need to join by ID, joining by record assumes order which I strongly suspect is not correct.

[sasnewbie5]

Thanks Reeza! Is using SQL queries the only way to do an inner join in SAS? So far that's all I've found for creating an inner join.

[sasnewbie5]

Thank you! You mentioned inner joins. All of the examples I've found online are showing it using only SQL statements. Is that the only way to address what you mentioned?

[novinosrin]

I am glad, now you are talking and I am glad I mentioned my assumptions in those 2 points It;s safe to do the inner join and since you have different var names for id's, use sql like

proc sql;
create table want as
select name,salary
from one , two 
where empid=id;
quit;

Basically, you can avoid rename and sort with sql joins as opposed to datastep merge, and since it's a one to one, it's straight forward 

[sasnewbie5]

Got it, thanks so much for the clarification!

[Reeza]

---

@sasnewbie5 wrote:

Thank you! You mentioned inner joins. All of the examples I've found online are showing it using only SQL statements. Is that the only way to address what you mentioned?

---

data want;
merge one (in=a) two (in=b rename=(id=empid)) ;
by empid;

if a and b;

keep name salary;
run;

[sasnewbie5]

Thank you so much Reeza!

[Reeza]

A screenshot means someone needs to type out your data to work with it.
Instructions on posting your data set is here:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Reeza]

See the different examples of merges in the documentation and find the one closes to your situation. Pretty sure it's covered here:
https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1i8w2bwu1fn5kn1gpxj18xttbb0.htm&doc...

---

@sasnewbie5 wrote:

I don't follow. The code I have is as follows:

/*******/

data Dept1;
set hw8.dept1names;
set hw8.dept1salaries;
run;

/******/

Since I haven't specified any variables, how would I rename it? I'd prefer not to adjust any of the actual dataset content.

Also, I have the two datasets that I'm referring to but I'm not sure if I can upload them to this thread or not. As you can probably tell, I'm very new to SAS and the SAS community.

---