topic Re: Combining datasets in SAS Data Management

Combining datasets

Missmichelle — Wed, 06 Jun 2018 20:52:06 GMT

Hello everyone i'm working on a project to combine two datasets which I successfully imported into sas separately.

The first dataset is census data for an entire state by county and so each county is listed with its corresponding population estimate.

The second dataset is data that lists the number of teachers per county with many other variables. I am trying to merge these two datasets but using the merge statement is not matching them correctly. Both datasets are sorted by county. I also tried proc SQL and nothing. Any ideas? below is my syntax.

Im running version 9.4

Failed merge:

data CM;
merge One Two;
by cou;
run;

Failed proc sql:

Proc sql;

create table cmt as

select * from One a

join Two b

on a.cou=b.cou;

quit;

Thank you!

Re: Combining datasets

Reeza — Wed, 06 Jun 2018 21:03:12 GMT

What does not matching them correctly mean? Can you provide examples of records where you would like to see a match and it doesn't match? Matches are case sensitive so 'sas' is not the same as 'SAS'.

Re: Combining datasets

Kurt_Bremser — Wed, 06 Jun 2018 21:03:39 GMT

Your codes seem to be OK. Please post some example data against which we can test code (see my footnotes for how to do it).

Re: Combining datasets

ballardw — Wed, 06 Jun 2018 21:30:33 GMT

"Failed" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Combining datasets

TomKari — Wed, 06 Jun 2018 22:55:14 GMT

1. I suggest you stick with PROC SQL for this kind of merge. I think you'll find it conceptually simpler.

2. From your description, it sounds like you should have one record per county id in each file. If you have more in one file, or much worse both files, you're going to get confusing results. You might want to check that.

Tom

Re: Combining datasets

NeerajS1104 — Thu, 07 Jun 2018 13:05:38 GMT

Hello ,

a)Did you write appropriate type of join in the proc sql..

that might help the case...

Like .... 'inner' or 'left' or 'right' .....

For eg. below..

b) Are the datatype and length of county in both datsaets same ?

Proc sql;

create table cmt as

select * from One a

inner join Two

on a.cou=b.cou;quit;

Re: Combining datasets

Missmichelle — Thu, 07 Jun 2018 14:36:01 GMT

Thank you so so much!

Re: Combining datasets

Carry — Wed, 13 Jun 2018 16:53:41 GMT

Couple of thoughts.

First make sure each county is unique. Many Counties have the same name in different states. For example, there is an Orange County, TX and Orange County, CA.

You may need to get the variable State into your Teacher dataset and use both State and County to preform the merge.

proc sql;

create table cmt as

select * from One a

FULL join Two b

on a.cou=b.cou and a.STATE = b.STATE;

quit;

You will get a message about variables that are in both datasets. You might want to consider doing a COALESCE() on those variables that are in both for example cou and state.

Carry