Help using Base SAS procedures

Joining dataset from an import with a SAS dataset

Reply
Contributor
Posts: 24

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

 

Grand Advisor
Posts: 16,905

Re: Joining dataset from an import with a SAS dataset

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

Contributor
Posts: 24

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

Grand Advisor
Posts: 10,052

Re: Joining dataset from an import with a SAS dataset

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

 

 

Contributor
Posts: 24

Re: Joining dataset from an import with a SAS dataset

Thanks. I will give that a shot

Contributor
Posts: 24

Re: Joining dataset from an import with a SAS dataset

Do I use DATA WORK._99241_99245_Edited or just _99241_99245_Edited

Grand Advisor
Posts: 16,905

Re: Joining dataset from an import with a SAS dataset

You need DATA but not the WORK. 

 

 

Contributor
Posts: 24

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;

Grand Advisor
Posts: 16,905

Re: Joining dataset from an import with a SAS dataset

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
  • 70 views
  • 0 likes
  • 3 in conversation