Desktop productivity for business analysts and programmers

Register flat file formatting in EG

Reply
N/A
Posts: 0

Register flat file formatting in EG

I am new to EG, but have a client who is interested in EG. After demoing the power and user firendliness of EG, the client is inquiring if they can port over the existing flat file created into EG without the need to specify/register the formatting (eg. char/numeric/length) again. Their current ETL software (non SAS) has output a flat file and also a dct file (basically a data dictionary of the flat file structure).

Appreciate your feedback.
SAS Super FREQ
Posts: 8,721

Re: Register flat file formatting in EG

Hi:
I'm not sure what you mean by a .dct file. I've found 2 references and either one could be what you're talking about -- or it could be something entirely different. On Answers.com, http://www.answers.com/topic/dct?cat=technology, it implies that a .dct file extension could be used by any of these products: Microsoft Visual FoxPro, Clarion Database Developer, Harvard Graphics 3.0-Symphony.

So, if by DCT, you mean a FoxPro dictionary file, or some other kind of data base dictionary file, if the file can be accessed via ODBC or OLE-DB, then perhaps you can use SAS and ODBC or SAS and OLE-DB to read the database file without reading the dictionary file directly.

If by .DCT, you mean the kind of file that's described here, relating to reading Current Population Survey data, consider this fragment from a .DCT file from this site: http://www.nber.org/data/progs/cps/cpsmar07.dct
[pre]
infile dictionary {
*by Jean Roth Tue Aug 28 11:09:27 EDT 2007
*Please report errors to jroth@nber.org
*See the .do file for directions and run with do cpsmar07
*NOTE: This program is distributed under the GNU GPL.
*See end of this file and http://www.gnu.org/licenses/ for details.
_column(1 ) byte hrecord %1f
_column(2 ) long h_seq %5f "Household sequence number"
_column(7 ) byte hhpos %2f "Trailer portion of unique household"
_column(9 ) byte hunits %1f "Item 78 - How many units in the"
_column(10 ) byte hufaminc %2f "Family income"
_column(12 ) byte h_respnm %2f "Line number of household"
_column(14 ) int h_year %4f "Year of survey"
... more ...
[/pre]

This .DCT file defines a particular layout of population data. The person who maintains these programs, has provided an example of how to use SAS to read the file described by this .DCT file. Here's the SAS input statement that corresponds to the above .DCT :
(from http://www.nber.org/data/progs/cps/cpsmar07.sas)
[pre]

INPUT
@1 hrecord 1.
@2 h_seq 5.
@7 hhpos 2.
@9 hunits 1.
@10 hufaminc 2.
@12 h_respnm 2.
@14 h_year 4.
... more ...

[/pre]

So to answer your question...would EG read the DCT for the flat file directly and then import the flat file? No.

Could you use the current DCT file to code an INPUT statement for SAS so the file could be ported over to SAS format? Yes.

Once you have the SAS INPUT statement and program written, could you save it so the code could be reused on another flat file with a similar layout (or .dct)? Yes. You could even use a SAS program to read the DCT file and generate the SAS INPUT statement -- but that would be a more advanced programming task.

So, while there might be some time involved getting the program coded one time, using the .dct file as a reference, you could save and reuse that file over and over to read new flat files with the same layout. If you had SAS Data Integration Studio as part of the BI Platform, you could even make an ETL process and job to turn the flat file into a SAS data set. But even without DI Studio, you could set up a project in EG to load data into SAS. There would be coding involved -- but it's not too burdensome. As you can see from the above sample, there is a fairly straightforward one to one correspondence from the .DCT file that I found and the INPUT statement that SAS would need to read the same data. (Note, the Labels and lengths are all specified separately in her example program.

If your .DCT file does not look like the sample .DCT file or if you are talking about some other kind of .DCT file, then you might consider contacting Tech Support or a SAS consultant for more help figuring out the best way to get the data from your flat file format into SAS format.

cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 103 views
  • 0 likes
  • 2 in conversation