Date:         Wed, 23 Oct 1996 11:37:09 -0400
Reply-To:     TIM.LATENDRESS@LATENT62.CUSTOMS.SPRINT.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Tim Latendress <TIM.LATENDRESS@LATENT62.CUSTOMS.SPRINT.COM>
Subject:      Re: SAS/Connect how do I write a view?

Jeff Miller <jmiller@CLARK.NET> writes:
 
>I am currently working on a MVS/ESA system using SAS6.09.  We are going
>to use SAS/ACCESS and SAS/Connect to get from SAS MVS/ESA to SAS/PC
>6.11 then to extract data from Microsoft Access.  I do not understand
>where the view to extract data from Microsoft Access comes in...  I
>understand that SAS will be use the view to extract the data, but do we
>write the view in SAS/PC and execute in that environment?  Can anyone
>supply a sample of what a view looks like?  I understand it is written
>in SQL.
 
You'll need to create and execute your view with SAS on the PC.  If
you're using SAS/ACCESS to ODBC, then the following example should get
you on your way.  The code creates a permanent SAS view called
SASLIB.INSIGHT to read from the MS Access database 'c:\access\monthly'
table 'OCTOBER'.  To use it, simply use the view as if it were a SAS
dataset.  You can also add a WHERE clause to limit access to certain
information in the MS Access table.  In your case, you could probably
execute the following SAS/CONNECT PROC on the PC to read the MS Access
table and upload it to your mainframe session in one fell swoop
(although I'm not sure how efficient it would be).
 
  PROC UPLOAD DATA=SASLIB.INSIGHT;
  RUN;
 
Also, when creating your view(s), beware of long field names in your MS
Access table, SAS will truncate them.  To control this, you can change
the field name using the SELECT statement (e.g. SELECT Long_field_name
as long_fld, Other_field_name as othr_fld).
 
The SAS manual to read is SAS/ACCESS Software for Relational Databases,
Pub Order #55144 (specifically the SQL Pass-Through-Facility chapter).
 
>We have been told that once the view is written, it is not advisable to
>change the view?  Is this true?  Should we write the view for every
>possible field that we may want from the Microsoft Access system?
 
In an ideal situation you will have the system resources necessary to
read every possible field in every database table.  Some data warehouse
developers even suggest that you do just that.  However, in my case, MS
Access is sooooo easy to use that non-IS people are creating their own
databases. These databases often contain extraneous fields (e.g. one
field contains the first 4 characters of another field in the same
table). Even if it goes against suggested data warehouse guidelines, I
don't read them in my view.
 
If your SAS/ACCESS view is going to be used in a production system, then
any changes should be coordinated with all users. I don't know how much
control you'll have over the MS Access database(s) but changes are
probably inevitable. The important thing will be communicating these
changes, not the changes themselves.
 
> <snip> Any information is appreciated, our shop is pretty much
>mainframe dinosaurs and we hope to enter the PC world.  Thanks for your
>time.
 
With mainframes supposedly shipping at an all-time high, I'd say that
we're more like live alligators than extinct dinosaurs!  We're feasting
on those who have returned to drink at the mainframe processor pool :-)
 
 
HTH
 
Tim (I haven't been turned into a pair of shoes yet!) Latendress
latendress@latent62.customs.sprint.com
 
--------------------------- Sample Code --------------------------------
 
* Submit this on the PC ;
 
options symbolgen;
 
* Allocate a directory to hold the SAS view ;
libname saslib 'c:\saslib';
 
* Create the view of the MS Access table ;
PROC SQL;
 
   * Connect to your ODBC Data Source (must already be configured);
   CONNECT TO ODBC as MSACCESS (REQUIRED="dsn=MS Access 2.0 Databases");
 
   * create the view from your MS Access data source;
   CREATE VIEW saslib.insight AS
   SELECT *
      FROM CONNECTION TO MSACCESS
      (SELECT *
       FROM "c:\access\monthly"."OCTOBER");
 
   * Disconnect from your Data Source;
   DISCONNECT FROM MSACCESS;
 
QUIT;