Import text file to oracle table through SAS-EG

[deleted_user]

hi,
I had imported one text file data which is pipe delimited to oracle table through SAS-EG import wizard. The problem is it has taken the column width just based on the first few records. because of this for some of the column values where the the size of the values are different from the first few records, it has just exracted only those number of characters as the size of the characters in the first few records.

So I want the column width of the oracle tables should be such that it doesnot skip any character from the original input file.

I know my explaination would be very confusing in words.
I am trying to explain with one example.
NOTE:- the input file is pipe delimited
supoose the the input file is input.txt. It contains datas as follows:
first_nm|last_nm|zip_code
a|b||
c|d||
e|f||
--
--
--
x|y|12345
p|q|2345676

So while using the import wizard. its making the column width for the column zip_code with size 1 based on just taking the first few records containing null values.
but actually the zip_code values size can be more as we can at the last records(12345,2345676). the import wizard is taking only the first character from the last records(i.e. 1,2).

Please give any solution for this. one option is there to specify the column width size at the time of importing through import wizard. but I dont want to check the maximum size of the column from the input file.

[Cynthia_sas]

Hi:
When I import data from the EG 4.1 menu by selecting
File --> Import Data...
then select Local Computer
Next, the Import Data window opens

On the left hand side of the Import Data window, there are 4 selections you can make:
Region to import
Text Format
Column Options
Results

I find that if I select "Text Format" on the left, then I can specify the pipe symbol as the delimiter on the right pane. Next, I can select "Column Options" on the left and see the Type (Character or Numeric) and Length that will be used for each variable or column that will be imported. The menus may be slightly different in EG 4.2, but you should still be able to find some method for changing column options.

cynthia

[Patrick]

With EG 4.2:

You can change the informat in step 3 of the import wizard. For an already created import node: right mouse click on node, modify.

About choosing the server for processing:
Let's say you have your source data on your local PC where SAS EG sits, but the server able to access the Oracle DB is not "local" but "SASMain or "SASApp": You will have to process your data on the remote SAS Server.

I realised the checkbox in the EG import wizard, step 4 saying "Embed the data within the generated SAS code". I think this could be a good idea for a once-off job.

The specialised SAS tool for creating ETL processes is of course not SAS EG but SAS Data Integration Studio.

HTH
Patrick

[deleted_user]

Thanks cynthia and patrick for considering my problem.

But actually my problem is little different.
I am able to import the pipe delimited text file into a table through the SAS-EG 3.0 import wizard but its not taking the actual data type and width as in the actual i/p text file.
This is because its only making the column data type and widht based on the first few records.
By specifying the column type and width manually at the time of importing may solve the problem but in that case I need to find the type and maximum size of each column by looking into the input file which is too large.
So is there any way through wizard or through sas code so that it can pick the ACTUAL data type and width while loading the input file.

[Patrick]

SAS EG 3.0! That means you're still running your code under SAS 8.

There would be the option "guessingrow" but its best implementation with the least issues is under 9.2 only.
http://www2.sas.com/proceedings/sugi30/038-30.pdf
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000312413.htm

"...but its not taking the actual data type and width as in the actual i/p text file..."
The question is: What is the actual data type and width? You should know that in advance (record description or the like). Even if SAS guessing does the right thing it doesn't mean that the type and length will be appropriate for the next time you read such a source csv file.

If this is a once-off job then I would let the import wizard create the source and then start to alter the informats very generouse (mainly: longer characters), run the code and if it falls over apply the change for the var where you had the problem (may be then running with a firstobs=... starting at the point where your code fell over the last time).

If this is something regular: You should try and get a record description from the producer of the source data. Everything else is a work-around.

You can of course write a piece of code which scans the full source data in a "data _null_ step" and determines the maximum length and data type. You would then save the results into macro variables (the informats/lengths) and use them in a second data step where you actually load the source data into a SAS dataset. But as you can see: This involves some coding.
Try and "Google" a bit: I would expect that someone already solved a comparable problem.

HTH
Patrick

[ChrisHemedinger]

With EG 3.0, you might have some luck using the schema.ini file:

http://support.sas.com/kb/2/583.html

Chris