topic Re: Problems in appending two tables in SAS Enterprise Guide

Re: Problems in appending two tables

deleted_user — Tue, 01 Aug 2006 12:28:19 GMT

What I want to do is to combined table A and table B to become Table C according to the column of 'name'as below:

Table A
Name Age Birthdate
David 12 12 august
Michael 13 14 august

Table B
Name Age Place Married
David 12 Town Yes
Aileen 14 Uptown No

Table C
Name
Name Age Birthdate Place Married
David 12 12 august NA NA
Michael 13 14 august Town NA
Aileen 14 NA Uptown No

1. How do i come across in doing that?
2. What do i do to the column that has same attribute in table A and table B? Should I match them as well ? What if 2 tables have same column name but different observation, would there be an error ?

Thank you

Re: Problems in appending two tables

Cynthia_sas — Wed, 02 Aug 2006 20:46:55 GMT

Hi,
What you want to do is not generally considered to be appending the two tables. Consider this example, where both files have the same variables
[pre]
FILE1 FILE2
----- -----
grp num grp num
AA 111 XX 999
BB 222 YY 888
CC 333 ZZ 777
[/pre]

If I appended those two tables together, one on "top" of the other, I would get a file that looked like this:
[pre]
Second file appended to first file:
grp num
AA 111
BB 222
CC 333
XX 999
YY 888
ZZ 777
[/pre]
On the other hand, if my 2 data files looked like this:
[pre]
OrderFile PaidFile
---------- --------
name type owed name type paid
Alf web 100 Alf web 100
Alf phone 300 Alf phone 300
Bill web 400 Bill web 400
Bob phone 222 Dora web 200
Dora web 333
[/pre]

And I wanted to join the two table together or combine them "horizontally", I might want my resulting table to look like this:
[pre]
Newfile
-------------------
name type owed paid
Alf web 100 100
Alf phone 300 300
Bill web 400 400
Bob phone 222 .
Dora web 333 200
[/pre]

In EG, you would need to build a query that performed a JOIN on the two tables. EG has a graphical query builder that lets you join 2 or more tables together. For more information, consult the EG Help files. In the table of contents for EG topics, look for the "Building Queries" topic and for the "Joining Tables" topic. Also under the "Selecting Data" topic, you will find information about selecting columns for your query.

cynthia

Re: Problems in appending two tables

deleted_user — Thu, 03 Aug 2006 10:21:18 GMT

Thank you Cynthia,

I just did that like this:

Data NewFile;
merge file1 file2 file3 file4;
by ID;
Run;

This is like putting whatever variable in all the file into the newfile. So the newfile contain all the variables in all the files (4 times the number in total ).
Also, to do this, the ID has to be sorted in asceding or descending order beforehand.

**************************

However, I still haven't figured out whether it still work if file1 and file2 has more than 1 similar variable ( Maybe ID and Place )

*****************************

Re: Problems in appending two tables

Cynthia_sas — Thu, 03 Aug 2006 13:35:28 GMT

Hi!
I see that you used the Merge statement. There are a lot of different issues to consider when you are using a merge. For example, a merge by ID and Place would be entirely possible and depending on your data, might give you different results than just a merge by ID. The data step merge works best, in my opinion, if you are doing one-to-one or one-to-many merges.

You can control which variables come from which dataset by using the (DROP=) or (KEEP=) data set option in your Merge statement. You can further control your processing by using the (IN=) option for each data set -- because it will give you a variable name to test in an IF statement.

Describing everything you need to know about how MERGE works could take a LONG time. I suggest you look up MERGE in the documentation and/or consult some of these user group papers (listed below) or contact Tech Support for help. One technique that works really well for me is to create several small data files, which would allow my code to test all the possible combinations of the data, then once the test data is combined correctly, I know how my MERGE is going to work and I point my program at the production data.
cynthia

** some papers **
http://support.sas.com/techsup/technote/ts644.html
http://www2.sas.com/proceedings/sugi28/096-28.pdf
http://www.ats.ucla.edu/STAT/sas/library/nesug99/ad155.pdf