Help using Base SAS procedures

Joining dataset from an import with a SAS dataset

Reply
Contributor
Posts: 59

Joining dataset from an import with a SAS dataset

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

 

Super User
Posts: 23,724

Re: Joining dataset from an import with a SAS dataset

Posted in reply to wheddingsjr

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

Contributor
Posts: 59

Re: Joining dataset from an import with a SAS dataset

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

Super User
Posts: 13,542

Re: Joining dataset from an import with a SAS dataset

Posted in reply to wheddingsjr

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

 

 

Contributor
Posts: 59

Re: Joining dataset from an import with a SAS dataset

Thanks. I will give that a shot

Contributor
Posts: 59

Re: Joining dataset from an import with a SAS dataset

Do I use DATA WORK._99241_99245_Edited or just _99241_99245_Edited

Super User
Posts: 23,724

Re: Joining dataset from an import with a SAS dataset

Posted in reply to wheddingsjr

You need DATA but not the WORK. 

 

 

Contributor
Posts: 59

Re: Joining dataset from an import with a SAS dataset

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;

Super User
Posts: 23,724

Re: Joining dataset from an import with a SAS dataset

Posted in reply to wheddingsjr

Fastest way is to try it Smiley Happy

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;

 

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 184 views
  • 0 likes
  • 3 in conversation