Read data from Qlikview into sas

[ledaiduongvnth]

Hi guys!

I have a qlikview data file.The file includes:

-An XML formatted table header, after loading XML table header in sas system, i received some sasdatasets that describe about structure of qlikview data file, i made empty sasdataset that correcpond with structure of qlickview data file.

-The actual data like that: " à@ à@ à@ @ ÁZ¦EÀ ÁZ¦EÀÁZ¦EÀ ", but i don't know what is format data is stored in?

Now i need load actual data into this empty sasdataset, but i don't know how can i do. My leader suggested using ''READING BYNARY DATA". I tried read by using statement INFILE and INPUT with ENCODING, but my implements aren't successful because very hard determine Binary Informat.

Can somebody give me suggestion ? thank so much!

[RW9]

Right, so just checked on this.  QVX files, the one you have read in are XML and describe the dataset.  So the data is not in the same file?  Is it a QVD file?  If so then you have real problems as that is a proprietary file format (binary and compressed).  You would need to know the file structure, for which you would likely need a developer license from that company.  Its not just a matter of reading the binary data (and uncompressing) but understanding the file format.  You would be simpler getting a tool to do this and save to CSV, one I found:

https://www.etl-tools.com/q-eye/overview.html

[ledaiduongvnth]

Thank Sir so much for answer !

Data is in the same file, look at data i can know the rows of table are distributed sequentially and i knew that first byte in head of ech cell reference length of this cell. 

i know that can save to CSV and read by sas simpler, but my main purpose is learning sas besause i want to try read binary data. If you have any idea tell me please.

In attachments is raw QVX file, please look at this .

[RW9]

Unfortunately as I mention above the file format is a proprietary file format.  You need to know the structure of the data, and how to uncompress it, which is generally only available for those with licenses.  I would suggest asking Qlikview or their forums on the file format and see if there is something available.

It a good example of why to avoid proprietary file formats like the plague.  I would do anything to avoid using these types of files.

[Tom]

To read binary data you probably will want to use the proper format.

To get an idea what you binary data actually looks like use the LIST command in a data null.  If the records have non-ASCII characters then SAS will dump the hex code of the characters in two lines below the location in the line.

So if you have variable length character string represented by a 2 byte binary integer length followed by the text your input statemens for that part of the record might look like:

input len pib2. @;
input charvar $varying32767. len @ ;

But if your file actually has some type of compression (think ZIP or GZIP) done then you should first run it through some decompress utility first before trying to read it with SAS.

You might want to start with a simplier binary file format.  Why don't you look up the format for a DBF file and try to read one of those?

[ledaiduongvnth]

Tom, thank so much, look at the result, i get it after using your guide, this result don't like thinks that i want to get but better then i got !

data a;
infile 'C:\Users\d\Downloads\text.bin';
input len pib2. @;
input charvar $varying32767. len @ ;
run;

i want ask you this question, can i read 1 byte instead reading 2 byte ? I tried this code but it didn't work 

data a;
infile 'C:\Users\d\Downloads\text.bin';
input len pib1. @;
input charvar $varying127. len @ ;
run;

Can you explain me how determine number after 'varying'? And if you have some documents about that give me please?

[Tom]

---

data a;
infile 'C:\Users\d\Downloads\text.bin';
input len pib1. @;
input charvar $varying127. len @ ;
run;

Can you explain me how determine number after 'varying'? And if you have some documents about that give me please?

---

i want ask you this question, can i read 1 byte instead reading 2 byte ? I tried this code but it didn't work 

You should be able to use PIB1. to read a one byte binary character as an integer. Set the width used in the $VARYING. informat based on the maximum number of characters that could be in that field.  So if your length is specified by a single byte then the maximum will be 255.  Also make sure that the variable you are reading the string into is long enough.

Use the LIST statement to see what your data looks like.  For example you might try reading it using fixed length records just to make it easier to read the logs.  Here is a little program to read the first 1,000 bytes of a file in records of length 100.

data _null_;
  infile 'C:\Users\d\Downloads\text.bin' lrecl=100 recfm=f obs=10 ;
  input;
  list;
run;

Use the $HEX. format to see the codes for the characters that you have already read into a variable.

data _null_;
  infile 'C:\Users\d\Downloads\text.bin' recfm=n ;
  input str $char50. ;
  put str $hex100. ;
run;