topic Re: Joining dataset from an import with a SAS dataset in SAS Procedures

Joining dataset from an import with a SAS dataset

wheddingsjr — Tue, 02 May 2017 21:31:23 GMT

Hello all

I have run a query in SAS and have my results in a table called "RESULTS", I also imported a excel sheet into the program labled "CONSULT". I want to get a dataset where the two tables are joined to retrieve certain data. I was told that was possible but when I ran the below query I got the error message that follows the query. Any suggestions would be helpful.

proc sql;
create table endresult as
select a.*
from results as a, Consult as b
where a.memno = b.memno and a.SProv_Spec = b.SProv_Spec and a.Prov_TAXID = b.Prov_TAXID
order by a.memno, a.SProv_Spec, a.Prov_TAXID;
quit;

ERROR: File WORK.CONSULT.DATA does not exist.

Thanks

William

Re: Joining dataset from an import with a SAS dataset

Reeza — Tue, 02 May 2017 21:46:59 GMT

Show the code/log from the import of the Excel file - it looks like it wasn't created properly.

Re: Joining dataset from an import with a SAS dataset

wheddingsjr — Tue, 02 May 2017 21:56:07 GMT

I just thought of something. As I was getting ready to attach the code/log, I realized I changed the name of the file to "Consults" in both the code and the log it reads:

DATA WORK._99241_99245_Edited

That was the original name of the file. Could that be the problem or do you still need to see the log?

Thanks

Re: Joining dataset from an import with a SAS dataset

ballardw — Tue, 02 May 2017 22:00:56 GMT

Use that data set name instead of Consults and it may well work.

Re: Joining dataset from an import with a SAS dataset

wheddingsjr — Tue, 02 May 2017 22:09:20 GMT

Thanks. I will give that a shot

Re: Joining dataset from an import with a SAS dataset

wheddingsjr — Tue, 02 May 2017 22:12:57 GMT

Do I use DATA WORK._99241_99245_Edited or just _99241_99245_Edited

Re: Joining dataset from an import with a SAS dataset

Reeza — Tue, 02 May 2017 22:22:26 GMT

You need DATA but not the WORK.

Re: Joining dataset from an import with a SAS dataset

wheddingsjr — Tue, 02 May 2017 22:26:57 GMT

Like this??

proc sql;
create table endresult as
select a.*
from results as a, DATA _99241_99245_Edited as b
where a.memno = b.memno and a.SProv_Spec = b.SProv_Spec and a.Prov_TAXID = b.Prov_TAXID
order by a.memno, a.SProv_Spec, a.Prov_TAXID;
quit;

Re: Joining dataset from an import with a SAS dataset

Reeza — Tue, 02 May 2017 22:56:08 GMT

Fastest way is to try it 🙂

If you tried it and it didn't work, you should include your log.

Because you're using a SQL step, you only include the dataset name. I thought you were using a Data step, therefore needed the DATA keyword. Personally, I'll always explicitly define my join type, here you've left it blank which is a CROSS join (cartesian product) but I've changed it to LEFT JOIN and use the ON clause instead of WHERE. The different join types are illustrated below.

proc sql;
create table endresult as
select a.*
from results as a
left join _99241_99245_Edited as b
on a.memno = b.memno and a.SProv_Spec = b.SProv_Spec and a.Prov_TAXID = b.Prov_TAXID
order by a.memno, a.SProv_Spec, a.Prov_TAXID;
quit;