topic Re: Creating a list from two; some IDs match between the two lists and some don't in SAS Programming

Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Thu, 17 Jul 2014 18:54:24 GMT

G'd a y. I need to create a single list from two separate lists. One list has >10,000 observations; the other has a couple thousand.

Both lists have 7 digit character ID#s (I will list as single digit ID# below to make it easier to understand) . Some IDs match and some don't between the two lists.

I'd like to keep the ID, Name, Phys Street, PhysCity, PhysZip, and Email for the final list. How would I get a final list where each ID is listed once?

List One

ID SchoolName PhysStreet PhysCity PhsZip Email

List Two

ID SchoolName PhysStreet PhysCity PhsZip Email

Any help you can give is much appreciated!

Re: Creating a list from two; some IDs match between the two lists and some don't

stat_sas — Thu, 17 Jul 2014 19:18:51 GMT

proc sort data=one;
by id;
run;

proc sort data=two;
by id;
run;

data want;
update one two;
by id;
run;

Re: Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Thu, 17 Jul 2014 19:29:53 GMT

Appreciate your response! Correct me if I'm wrong, but I thought the update statetment updates values in dataset one with the values in dataset two that match but doesn't add the observations from dataset two into dataset one that don't match?

Final List

ID SchoolName PhysStreet PhysCity PhsZip Email

Re: Creating a list from two; some IDs match between the two lists and some don't

stat_sas — Thu, 17 Jul 2014 19:38:42 GMT

It does both. Observations in list one will be updated across the variables with the values of list two based on common ids and new ids from list two will also be added to list one to make it final list. I am assuming that ids are unique in both list one and list two.

Re: Creating a list from two; some IDs match between the two lists and some don't

Reeza — Thu, 17 Jul 2014 20:11:26 GMT

If the IDS match between lists, will the corresponding information, ie school name, street address automatically match as well?

If not, how do you want to deal with that? If using the second file is fine the solution above is appropriate. If you want the first file, flip the data sets on the update statement. If its some other logic you'll need different code.

Re: Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Thu, 17 Jul 2014 20:50:49 GMT

Good point. The addresses most likely will match between the two lists. How can you tell if they don't match (i.e, keep the ones in the final list that have the same ID but different addresses)?

Re: Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Thu, 17 Jul 2014 20:54:14 GMT

Appreciate you helping me understand the UPDATE statement better!!

Re: Creating a list from two; some IDs match between the two lists and some don't

Reeza — Thu, 17 Jul 2014 20:56:59 GMT

If this is manually entered data you'll also have issue with the address, where one person spells out street and the other uses st.

I would append and take a look and see if the multiples have differences first to determine what needs to be done to clean the data. The check dataset below will be all ID's with multiple records.

data temp;

set one two indsname=source;

DSET=source;

run;

proc sort data=temp; by id schoolname phystreet;

run;

data check;

set temp;

by id;

if first.id ne last.id;

run;

Re: Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Thu, 17 Jul 2014 23:15:36 GMT

Great -- appreciate you helping understand how to check the dataset!!

Re: Creating a list from two; some IDs match between the two lists and some don't

Ksharp — Fri, 18 Jul 2014 16:30:59 GMT

If I understood what you mean.

data a;
 set sashelp.class;
 id+1;
run;
data b;
 set sashelp.class end=last;
 id+1; output;
 if last then do;id+1;name='Arthur.T'; output;end;
run;

data once;
 set a b;
 by id;
if first.id and last.id ;
run;

Xia Keshan

Re: Creating a list from two; some IDs match between the two lists and some don't

jcis7 — Tue, 22 Jul 2014 21:53:51 GMT

Ksharp wrote:

If I understood what you mean.



data a;
 set sashelp.class;
 id+1;
run;
data b;
 set sashelp.class end=last;
 id+1; output;
 if last then do;id+1;name='Arthur.T'; output;end;
run;

data once;
 set a b;
 by id;
if first.id and last.id ;
run;






Xia Keshan

What does the following code refer to or do? name='Arthur.T';

Re: Creating a list from two; some IDs match between the two lists and some don't

Reeza — Wed, 23 Jul 2014 00:24:59 GMT

It adds a record in to data set B, with the name set as Arthur.T, this record would not be data set A.

It's a way of simulating your data.

Re: Creating a list from two; some IDs match between the two lists and some don't

Ksharp — Wed, 23 Jul 2014 12:16:24 GMT

Thanks. Reeza . Yes. I just make a dummy table to simulate your data .

Reeza , You gonna surpass Arthur.T to be number one of sas user list . Congratulations !

Xia Keshan