Loading SAS Data in Data Load Editor

[smusayev]

Hello I have data in form of fixed width file with txt extension that is over 1 gig in size.

With the file I have received a word document that describes at what position is each header supposed start and stop (as well as whether the file is a an integer or character, SAS only has 2 data of types).

Below is the example of the word document:

@  3           HOSPDIV                 $5.

@  8           NPI                    $10.

@  50          RECID                    2.

@  128         ZIP                     $9.

@  137         STATE                   $2.

@  139         COUNTRY                 $3.

@  142         RESCODE                 $4.

In SAS this would be in the infile step which would define the header position.  We don't have SAS in my organization, and I wanted to know if its possible to insert the data into Qlik and in the Load Editor define the header positions.

If its possible, can someone please provide an example of the script.  ( I believe this can be done visually but there are over 300 columns in file, which might take forever to do manually).

Please help.

Thank you,

[Gysbert_Wassenaar]

If the file is a sas data file then you need a sas odbc driver and possible more sas components to read data from that file. Or use a tool like EasyMorph that can read sas data files and output a qvd file.

If the file is a text file you can use the File Wizard to load the file and specify the positions that mark the start and end of the fields. It will look something like:

LOAD

     [@3:7] as HOSPDIV,

     [@8:49] as NPI,

     ....etc

FROM

     [myfile.txt] (fix, codepage is 1252, embedded labels)

If you're lucky you can use the Analyze Fix Positions option and have the File Wizard give you the correct field definitions. If you're out of luck you can also try pasting the content of your word document in excel and create lines qlikview script that define the fields correctly.

[#3]

I would take this Word document and pasted the list into Excel splitting it into 3 columns. Then used Excel formulas to calculate and concatenate positions and names in order to produce column definitions suggested by gwassenaar‌, i.e.:

[@3:7] as HOSPDIV,

[@8:49] as NPI,

     ....etc

Then I would copy the definitions and pasted into Qlik script, adding LOAD above and FROM with necessary properties below.