topic Re: How to create a new variable while using one-to-one merging in New SAS User

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

sasnewbie5 — Mon, 10 Dec 2018 17:48:31 GMT

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!

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

novinosrin — Mon, 10 Dec 2018 17:51:20 GMT

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

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

sasnewbie5 — Mon, 10 Dec 2018 17:55:44 GMT

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.

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

novinosrin — Mon, 10 Dec 2018 17:59:22 GMT

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

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

Reeza — Mon, 10 Dec 2018 18:03:39 GMT

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&docsetVersion=9.4&locale=en

@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.

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

sasnewbie5 — Mon, 10 Dec 2018 18:03:31 GMT

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.

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

novinosrin — Mon, 10 Dec 2018 18:14:03 GMT

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.

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

Reeza — Mon, 10 Dec 2018 18:18:17 GMT

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-data-AKA-generate/ta-p/258712

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

Reeza — Mon, 10 Dec 2018 18:19:16 GMT

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

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

sasnewbie5 — Mon, 10 Dec 2018 18:25:56 GMT

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?

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

sasnewbie5 — Mon, 10 Dec 2018 18:27:47 GMT

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.

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

novinosrin — Mon, 10 Dec 2018 18:31:10 GMT

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

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

Reeza — Mon, 10 Dec 2018 18:34:17 GMT

@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;

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

sasnewbie5 — Mon, 10 Dec 2018 18:34:33 GMT

Got it, thanks so much for the clarification!

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

sasnewbie5 — Mon, 10 Dec 2018 18:37:08 GMT

Thank you so much Reeza!