Desktop productivity for business analysts and programmers

Opening Flat Files in SAS EG

Reply
New Contributor
Posts: 2

Opening Flat Files in SAS EG

I am new to the SAS administration game. We just got our very first SAS Analytics Pro server online. However, I am having an issue with flat files opening in SAS EG. I have several users that have to grab datasets from our national mainframe. These datasets are often between 1-3gb in size. They usually encompass 1 full year of data related to the programmer's study project. The process includes the following:

1)The programmer writes a JCL sequence to query and extract the data set from the mainframe to a holding server. The dataset comes out as a flat transport file.
2)After extraction, the programmer pulls the transport file across our national network back to one of our local servers (usually via FTP).
3)After the file has been moved to our servers the programmer attempts to open the file in an EG session by either using a PROC Copy script or by doing a straight open procedure (File, Open, Data) from the File menu.

It here the process bombs. I know that there are easier ways to do this because I administer systems for a living. But my organization is a government agency and where there is government there is often red tape and inefficiency. This is not a show stopper but I need to get it to work. My heavy programmers are looking at years of data in one run so converting the transport files into something the EG can use will greatly increase performance. SAS is running on the following system:

Dell Poweredge 2950
*32GB RAM
*1.8TB native storage with SAS 15K H/Ds
*2 quad core Intel 5950 processors
*SAN-connected to Dell/EMC CX3-20 Clarion SP with 9TB native storage, 4GB FC backplane

SAS Platform
*SAS Analytics Pro (SAS Base 9.1.3 Network Version) (the latest vesion)
*Graph
*Stat
*Access/PC Files
*Access/ODBC
*QC
*ETS
*Intergration Technologies
*Enterprise Guide 4.1

Any help in licking this one would be greatly appreciated.
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

I know this sounds silly, but what about copying the files over from tape and dumping it into a local disk? I'm facing similar problems and I wish sas would offer more advice with regards to handling disparate sources of data (some of which come in different forms, via different modes of transport via odbc or sent over as flat files). If alot of the old information is static, I wonder if it would be a worthwhile investment to convert most of the legacy information into OLAP (which sas does have an OLAP server product)
-
Just out of curiosity, did you take any steps to tune the filesystem and the network settings (like setting max number of tcp connections) for the Dell PE box and the connected storage subsystem? I'm familiar with how this is done on linux, but I'm stumped replicating the experience on wintel. Message was edited by: Joshua
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

You should try to import the data, not open the data.
File -> Import Data ...

The flat file, if from a mainframe, should be a flat, fixed column, text file.
If not, then comma or tab delimited text.
If not, if there are binary fields -- packed decimal, for instance -- then the world is a more difficult.
SAS Super FREQ
Posts: 8,819

Re: Opening Flat Files in SAS EG

Hi:
My concern is the mixed terminology in the question. At one point in the post it says: TRANSPORT as well as FLAT file and than at another point in the post, it refers to PROC COPY.

So IF it is a SAS Transport file that was created, then FILE --> Import may not work. They may need to use PROC CIMPORT to open the file. The CIMPORT procedure imports a transport file that was created (exported) by the CPORT procedure. ...as described here:
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/cimport-overview.htm

As opposed to XPORT files, that need to be read with either the DATA step or PROC COPY using the XPORT engine.
http://support.sas.com/documentation/cdl/en/movefile/59598/HTML/default/a002576216.htm

So in this case, clarifying exactly WHAT kind of file was created, HOW it was created on the mainframe (what syntax), determines HOW it will need to be read in EG. (Probably for the above 2 possibilities -- in a code node and not through FILE --> Import)

And there's a further note that:
Transport files that are created using PROC CPORT are not interchangeable with transport files that are created using the XPORT engine.

And, this note
http://support.sas.com/kb/16/800.html
explains that EG does not support .XPT files (XPORT) files for automatic import, but instead outlines a code method that you must use to read the data.

At any rate, I'd recommend gathering specific information on HOW the files were created on the mainframe and then contact Tech Support for more help reading the files using EG -- because the method of reading the files is going to depend entirely on the answer to that question.

cynthia
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

This begs another question: are flat files the only and best way to import data from mainframes (or even other servers accessing sas data)? I am under the impression that users typically export their sas data from one site as a flat file, so it can be easily portable to another site or server. Can flat files be broken into small segments (to facilitate faster and easier ftp transfer)? This becomes a big issue when the flat file grows well above 2 GB.
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

I was assuming that they did not have SAS on the mainframe, and that other tools produced the files for IMPORT.

If SAS is on the mainframe, then I would recommend obtaining SAS/CONNNECT and/or SAS/SHARE to allow using the data on the mainframe directly with EG and not have to create a file and copy it to another SAS server.
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

but why flat files? why not .sasb7dat? is there a legacy reason for so many to be running flat files?
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

Only SAS can produce the .sas7dat files.
To get the data into SAS to create a .sas7dat file, you have to read it in.
The available sources are:

1) A database -- Oracle, DB2, etc.
2) VSAM files -- mainframe only
3) PDS files -- mainframe only, a file that is a libary of files, like a zip or tar file.
4) "flat files" which could be text, or binary.

.sas7dat files can be compressed by SAS and ftp'd, or compressed with gzip or "compress", ftp'd, and then uncompressed/ungzipped.

SAS can do the ftp itself with "FILENAME fileref FTP 'remote_filename' ... ; "

I have a process that accesses Nortel Passport datafiles on a remote server using 'FILENAME fileref FTP "*_&date_*.bdf" cd "/opt/passport" ... ; ' and then "data passport; length filename $256; infile fileref filename=filename; ..."
SAS Super FREQ
Posts: 8,819

Re: Opening Flat Files in SAS EG

Hi:
First, until the person who asked the original question clarifies WHAT kind of file (FLAT file vs TRANSPORT file) it's impossible to make a good recommendation.

As for your question of why flat files for moving data around ... some thoughts:
1) legacy processes -- especially with mainframe files (EBCDIC files and packed decimal numbers) it was easier to move these to Windows or UNIX and then read them into SAS form. Flat files (sequential files) are highly compressable for storage and FTP purposes.

2) SAS7BDAT were only portable between Windows and Unix -- so if you had SAS files on the mainframe AND you had SAS on the mainframe, one option was to open a SAS/Connect session between the Windows/Unix machine and the mainframe and do a remote submit of a program to the mainframe and then get the results or subset down on the .

3) If you didn't have SAS/Connect, another possibility was SAS TRANSPORT format -- either XPORT files or files created with PROC CPORT. This allowed you to move SAS files across platforms & in fact, for clinical trial submissions to the FDA -- SAS Transport files were the required format.

4) If you did NOT have SAS on the mainframe platform, then you needed to use other mainframe programming languages to create a file that SAS could read -- after the file had been FTP's to either Windows or Unix -- the DATA step is very fast and very flexible for reading these kinds of files.

So those are some of the reasons why people did choose and still choose to create flat files as a mechanism for moving data cross-platform.

cynthia
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

If we remove the mainframe from the list of servers connecting to each other, could I then infer that SAS7BDAT is a better way for the servers to cache(store) the data and to pass the data around as physical files shared across the network (assuming you didnt have sas access).

Can sas7bdat files be converted back and forth to flat files easily? From what I recall, sas7bdat could actually be generated from sas 8 or 9 engines, so is there a way to tell which sas version it was created under at one look?

If there isnt sas connect, is there a commonly suggested file format to store and share the data?
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

MS Access stores its database in .mdb files
Excel stores its workbook, which contain worksheets in .xls files.
Microsoft uses .doc
SAS generates/uses .sas7bdat files; but, that is also configurable.
Base SAS is not an RDMS, like Oracle, DB2, Sybase, Informix, etc.
You can easily copy and move a .sas7dbat file around, from one directory/folder to another and from one server to another, of the same kind, and SAS can happily open it and use it without concern.

It is my understanding that you cannot take .sas7bdat file from a mainframe, ftp it to a Sun box and open it. The binary representations are totally different.

It is my understanding that you could have the same problem between Sun, AIX, HPUX and Windows.

My question is generally, why would you want to in the first place?

If data is in Oracle, why would you want to move that data to a text file and move it to a different server so that you could import it into another system? There are legitimate circumstances, but generally, it is a bad idea. It the data is in a database, it is there so that a variety of tools can access it directly from the database. Same goes for SAS, once data is in a SAS dataset, use that SAS to do stuff with it. Or use SAS/CONNECT or SAS/SHARE or other SAS tools for processing the data on a different box.

RDMS's like Oracle and DB2 are meant to hold all data of all kinds and divy it out to whomever wants/needs to for some purpose. So set up your SAS to access the database to pull the data you want to analyze, and maybe even do some intial manipulation with it there on the database server first.

SAS is so good at reading so many different types of files, that it generally doesn't matter what you choose for your "standard format" for transferring data files around: tab delimited, comma delimited, any delimiters, fixed field lengths, variable records, etc. etc. etc. Use what is most convenient/standard practice for the application/system exporting the data. Just be careful of XML because it can blow up the file size hugely with all the extra text.

Generally, generate text files, compress them, move them, uncompress them, import them. Although, I had a C program do some intial data summary work on a mainframe, save it as a binary file, and then read those binary files in with SAS for actual analysis work.

I used C because of the nature/format of the incoming data files, it was easier to use unions to read in the data, and then structs to output the summary results. This is one situation where SAS isn't as efficient as C unions for data reads. SAS doesn't deal as well with input data that breaks normal form 1. It can do it, but it is more painful, and less efficient. Message was edited by: Chuck
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

I suppose it falls back to other considerations. The reason to use physical files instead of an oracle database or an OLAP cube would probably be for portability within the subnet, and also to export the data out of your evironment to other agencies.

Given that the assumption is to share the data amongst wintel and unix sas boxes, what would typically be a good idea? I'd conclude that compressed flat files might be a bandwidth efficient way (if you arent sharing the data via a database connected across the network).
Community Manager
Posts: 2,889

Re: Opening Flat Files in SAS EG

Chuck,

It's true that .sas7bdat files have different encoding representations depending on the platform architecture.

However, SAS on any platform can read a .sas7bdat file from any other platform, using a feature known as Cross-Environment Data Access (CEDA).

When using CEDA, there are a few limitations perhaps on performance and ability to update the file in place. However, you can at least read the contents of the data and create a "native" copy with DATA step or PROC SQL, for example.

More info at:
http://support.sas.com/documentation/cdl/en/lrcon/59522/HTML/default/a002118677.htm

Chris
New Contributor
Posts: 2

Re: Opening Flat Files in SAS EG

My apologies, I have been away for a few days. The reason for the usage of the flat transport file are because my organization does not have a up-to-date mechanism for moving large SAS data sets from our legacy mainframe environment. I would love to put a CONNECT server in the middle and have my programmers' output to it. When my test subject attempted to open the file by importing it into the project, EG bombed. I suspect because of windows flaws with using certain types of file types.
N/A
Posts: 0

Re: Opening Flat Files in SAS EG

Ok, now you have really confused me.

1) Do you have SAS installed on the mainframe?
2) Where all do you have SAS installed, and what flavors?
3) CONNECT is not a separate "server" it is a SAS bit of software providing some connectivity features between one SAS server and another SAS server. Message was edited by: Chuck
Ask a Question
Discussion stats
  • 14 replies
  • 1393 views
  • 0 likes
  • 4 in conversation