topic proc sql in SAS Procedures

proc sql

Afor910327 — Thu, 29 Sep 2016 17:33:34 GMT

Hi all,

I have been trying to figure out this one but I cannot come up with anything to fix this error, since I am not a SAS expert, could someone explain this one so I can correct it?.... By the way, this is the first error of the log...

proc sql;

create table ql_a&currioyr. as

select a.*, b.io2_proval, c.io3_pi
from tempsas.io1_a&currioyr
join tempsas.io2_a&currioyr
on a.item_id = b.item_id and a.itemACPSA = b.itemACPSA
join tempsas.io3_a&currioyr
on b.item_id = c.item_id and b.itemACPSA = c.itemACPSA
;

quit;

MPRINT(PRICE_SERIES): proc sql;
SYMBOLGEN: Macro variable CURRIOYR resolves to 2012
SYMBOLGEN: Macro variable CURRIOYR resolves to 2012
SYMBOLGEN: Macro variable CURRIOYR resolves to 2012
SYMBOLGEN: Macro variable CURRIOYR resolves to 2012
MPRINT(PRICE_SERIES): create table ql_a2012 as select a.*, b.io2_proval, c.io3_pi from tempsas.io1_a2012 join
tempsas.io2_a2012 on a.item_id = b.item_id and a.itemACPSA = b.itemACPSA join tempsas.io3_a2012 on b.item_id = c.item_id
and b.itemACPSA = c.itemACPSA ;
ERROR: File TEMPSAS.IO1_A2012.DATA does not exist.
ERROR: File TEMPSAS.IO2_A2012.DATA does not exist.
ERROR: File TEMPSAS.IO3_A2012.DATA does not exist.
MPRINT(PRICE_SERIES): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: proc sql

Reeza — Thu, 29 Sep 2016 17:36:16 GMT

The error is pretty straightforward. SAS can't find the data set, the tempSAS.io1_a2012.

Is the dataset named correctly? Is it in the correct library?

Re: proc sql

Astounding — Thu, 29 Sep 2016 17:59:13 GMT

SInce you're asking the question, I suspect you will need a little more guidance.

To understand the program, you have to realize that wherever "&currioyr." appears, it will get replaced by "2012". That's why the program is trying to locate tempsas.io1_a2012, tempsas.io2_a2012, and tempsas.io3_a2012.

The first debugging step is to locate a statement that begins like this:

libname tempsas .......;

Take a look in the folder described by that statement, and see what data sets it contains. It may be that you have a totally wrong folder, or it may be that the folder is the right one but doesn't have any 2012 data sets in it, or it may be a slight misspelling, such as data sets named io1_a_2012 instead of io1_a2012. But look at what is in there. If it's totally the wrong folder, you will need to find the folder that contains the data sets io1_a2012, io2_a2012, and io3_a2012.

If by some chance you are working with a database, the idea is the same. Just look for a schema instead of a folder.

Re: proc sql

Afor910327 — Thu, 29 Sep 2016 18:15:53 GMT

It is named correctly, I can see it in the library but I do not know why in the world it does not find it.

Re: proc sql

Afor910327 — Thu, 29 Sep 2016 18:17:48 GMT

Thank you so much Astounding!