topic Relational Database in ODS and Base Reporting

Relational Database

deleted_user — Wed, 26 Jul 2006 19:13:01 GMT

Hi All,

Please I am having trouble with the syntax for working with relational databases. I have two SAS files on the same CD with a one-to-many relationship and one common field (ID No).
Please I need help with with the code/command to read 'simultaneously' from both files/datasets.

Thanks.

Re: Relational Database

DouglasMartin — Wed, 26 Jul 2006 22:38:10 GMT

Do you mean something more than doing a merge or SQL join?

data combined;
merge cd.file1 cd.file2;
by IDNo;
run;

If you like SQL you can do the equivalent in PROC SQL.

Re: Relational Database

deleted_user — Thu, 27 Jul 2006 13:09:52 GMT

Thanks Douglas.
However, it appears I'm still not doing something right (I'm really new to SAS).
Additional info: The IDNo represents individual patients. Each patient in file1 has many test results in file2.

I am interested in analyzing the data to see, for instance, the mean value of the test results for each patient, etc.
Can you help me with the code please, starting from reading the files (my cd drive is 'D').

Thanks for the trouble.

Re: Relational Database

DouglasMartin — Thu, 27 Jul 2006 20:19:03 GMT

If you're doing stats based just on the idno variable then you don't need to combine anything - just run proc means on the file2 dataset.

So, lets assume you want to display the name (presumably from file1) in place of idno, and do stats on result (presumably in file2).

I'm doing this off the top of my head, so there may be typos. I've included some other stats besides mean just to give you a better example.

libname cd "D:\";
data combined;
merge cd.file1 cd.file2;
by IDNo;
run;
proc means data=combined mean std median min max;
class name;
var result;
run;

If that's not enough explanation, you really need to consult the documentation.