BookmarkSubscribeRSS Feed
wheddingsjr
Pyrite | Level 9

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

 

8 REPLIES 8
Reeza
Super User

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

wheddingsjr
Pyrite | Level 9

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

ballardw
Super User

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

 

 

wheddingsjr
Pyrite | Level 9

Thanks. I will give that a shot

wheddingsjr
Pyrite | Level 9

Do I use DATA WORK._99241_99245_Edited or just _99241_99245_Edited

Reeza
Super User

You need DATA but not the WORK. 

 

 

wheddingsjr
Pyrite | Level 9

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;

Reeza
Super User

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;

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2191 views
  • 0 likes
  • 3 in conversation