BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Missmichelle
Fluorite | Level 6

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! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
NeerajS1104
Fluorite | Level 6

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

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.

 

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

ballardw
Super User

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

TomKari
Onyx | Level 15

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

NeerajS1104
Fluorite | Level 6

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;

Missmichelle
Fluorite | Level 6

Thank you so so much!

Carry
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1744 views
  • 1 like
  • 7 in conversation