topic Re: SASHELP.ZIPCODE HELP in SAS Programming

SASHELP.ZIPCODE HELP

Santha — Wed, 26 Feb 2020 20:09:42 GMT

Hi all. I am trying to use SASHELP.ZIPCODE in viya. I have a proc fedsql and i am trying to create a table by joining two simple tables called R and Z. Here is the code below. "Table "SASHELP.ZIPCODE" does not exist or cannot be accessed" is the error that I get. Is there a permission issue going on or something else not correct in the code below?

cas;
caslib _ALL_ assign;

proc fedsql sessref=casauto;
create table STDOPT.ModelingData as
(select R.Sitename,R.Sitezip,Z.X AS LATITUDE,Z.Y AS LONGITUDE
from SASHELP.ZIPCODE AS Z LEFT OUTER JOIN SANTSBOX.DATA as R
on Z.ZIP=R.Sitezip);
QUIT;

Re: SASHELP.ZIPCODE HELP

tomrvincent — Thu, 27 Feb 2020 12:57:43 GMT

If you do something like 'data foo; set sashelp.zipcode; run;' do you get the same error?

Re: SASHELP.ZIPCODE HELP

ballardw — Thu, 27 Feb 2020 21:51:11 GMT

@Santha wrote:

Hi all. I am trying to use SASHELP.ZIPCODE in viya. I have a proc fedsql and i am trying to create a table by joining two simple tables called R and Z. Here is the code below. "Table "SASHELP.ZIPCODE" does not exist or cannot be accessed" is the error that I get. Is there a permission issue going on or something else not correct in the code below?

cas;
caslib _ALL_ assign;

proc fedsql sessref=casauto;
create table STDOPT.ModelingData as
(select R.Sitename,R.Sitezip,Z.X AS LATITUDE,Z.Y AS LONGITUDE
from SASHELP.ZIPCODE AS Z LEFT OUTER JOIN SANTSBOX.DATA as R
on Z.ZIP=R.Sitezip);
QUIT;

It may help to post the ENTIRE proc step with errors from the log.

With a very simple query to fedsql I get:

8    proc fedsql;
NOTE: Writing HTML Body file: sashtml1.htm
9       create table work.junk
10      as select *
11      from sashelp.zipcode
12      ;
ERROR: Table "SASHELP.ZIPCODE" does not exist or cannot be accessed
ERROR: BASE driver, schema name SASHELP was not found for this connection
13   quit;

I use SASHELP.ZIPCODE frequently (not with FEDSQL though). I think that you need to use a special LIBNAME statement to make the BASE data sets accessible:

libname mybase base "<path to the SASHELP library where zipcodes resides.";

and use MYBASE>ZIPCODE.

I think part of the issue might be that the SASHELP library contains multiple directories as well. So a more specific path is needed.

Re: SASHELP.ZIPCODE HELP

maggiem_sas — Mon, 02 Mar 2020 16:33:02 GMT

See this SAS Note:

http://support.sas.com/kb/63/384.html

Be sure to click on the Full Code tab to see how to create the proper LIBNAME statement for Sashelp data sets.

Re: SASHELP.ZIPCODE HELP

Santha — Tue, 03 Mar 2020 21:06:59 GMT

yes i get the same error. i had to make a copy of the sashelp.zipcode then go from there. thanks for ur input though