Importing tab delimited online txt file

Reply
Occasional Contributor
Posts: 5

Importing tab delimited online txt file

Hi

I've read a lot of discussions in here but, since I'm a new guy in this SAS World, I couldn't find an answer. Perhaps someone can help me?

I'm trying to import a online txt file and it's tab delimited.

I've already came to the conclusion that I need a Program to do it.

I've tried this procedure:

https://communities.sas.com/message/144911#144911

modifying it according to my file but with no sucess.

Can someone help me?

Thanks!

PROC Star
Posts: 7,356

Re: Importing tab delimited online txt file

It would help if you showed the code that you tried, including the link to the file.

Occasional Contributor
Posts: 5

Re: Importing tab delimited online txt file

Hi

Thanks for your fast reply.

Here's the file:

https://dl.dropboxusercontent.com/u/80160/teste.txt

The code, I used the one in the other thread:

filename ex URL "https://dl.dropboxusercontent.com/u/80160/teste.txt";

data test;

infile ex firstobs=2 delimiter='09'x;

length DT_XXX $40;

length CHAVE_XXXX $40;

input DT_XXX $ CHAVE_XXXX $ XXXX XXXX_RC XXXX_DC XXXX_GS CC_XXX XXXXRSAL XX USER_XXXXXX XXXXX_ECU XXXX_XXXXXXX DXXX_XXXXXX XXXO PRXXXXX CAXXX PRXXXX XXE_XXXXDOR XXX_TOXXXXR SXXXX Tipo_XXXXX CAXXXXX

run;

A apologize in front for all the mistakes and "noob" errors that are on this code... Smiley Happy

Thanks!

Best regards

Super User
Posts: 10,458

Re: Importing tab delimited online txt file

As a minimum you want a semicolon to finish the INPUT statement.

Occasional Contributor
Posts: 5

Re: Importing tab delimited online txt file

True...

Didn't wrote it in here.

But it exists in the original code. Smiley Happy

PROC Star
Posts: 7,356

Re: Importing tab delimited online txt file

You will need to know more about the data in order to be confident that you are importing it correctly.  There appears to either: (1) an odd looking datatime variable and an odd looking number in the data, along with two fields that are missing all data; or (2) there are two tabs that should have been present but weren't.

The following will produce a file, but I don't think the correct one that the data really represent:

filename ex URL "https://dl.dropboxusercontent.com/u/80160/teste.txt";

data want;

  infile ex delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 ;

  informat DT_XXX anydtdtm40. ;

  informat CHAVE_XXXX best32. ;

  informat XXXX $17. ;

  informat XXXX_RC $14. ;

  informat XXXX_DC $8. ;

  informat XXXX_GS $21. ;

  informat CC_XXX best32. ;

  informat XXXXRSAL $21. ;

  informat XX best32. ;

  informat USER_XXXXXX $4. ;

  informat XXXXX_ECU $15. ;

  informat XXXX_XXXXXXX anydtdtm40. ;

  informat DXXX_XXXXXX anydtdtm40. ;

  informat XXXO $4. ;

  informat PRXXXXX $26. ;

  informat CAXXX $3. ;

  informat PRXXXX comma32. ;

  informat XXE_XXXXDOR best32. ;

  informat XXX_TOXXXXR $9. ;

  informat SXXXX $1. ;

  informat Tipo_XXXXX $1. ;

  informat CAXXXXX best32. ;

  format DT_XXX datetime. ;

  format CHAVE_XXXX best12. ;

  format XXXX $17. ;

  format XXXX_RC $14. ;

  format XXXX_DC $8. ;

  format XXXX_GS $21. ;

  format CC_XXX best12. ;

  format XXXXRSAL $21. ;

  format XX best12. ;

  format USER_XXXXXX $4. ;

  format XXXXX_ECU $15. ;

  format XXXX_XXXXXXX datetime. ;

  format DXXX_XXXXXX datetime. ;

  format XXXO $4. ;

  format PRXXXXX $26. ;

  format CAXXX $3. ;

  format PRXXXX comma12. ;

  format XXE_XXXXDOR best12. ;

  format XXX_TOXXXXR $9. ;

  format SXXXX $1. ;

  format Tipo_XXXXX $1. ;

  format CAXXXXX best12. ;

  input

          DT_XXX

          CHAVE_XXXX

          XXXX $

          XXXX_RC $

          XXXX_DC $

          XXXX_GS $

          CC_XXX

          XXXXRSAL $

          XX

          USER_XXXXXX $

          XXXXX_ECU $

          XXXX_XXXXXXX

          DXXX_XXXXXX

          XXXO $

          PRXXXXX $

          CAXXX $

          PRXXXX

          XXE_XXXXDOR

          XXX_TOXXXXR $

          SXXXX $

          Tipo_XXXXX $

          CAXXXXX

         ;

run;

Occasional Contributor
Posts: 5

Re: Importing tab delimited online txt file

Hi

Thank you so much for your answer.

I'm aware that are two collumns that, for this sample, are empty, but in the future, some values can appear. That's why they're there. Just in case...

I will try this code and tell you something.

Again, thanks for your effort.

Best regards

PROC Star
Posts: 7,356

Re: Importing tab delimited online txt file

While you are looking, examples of the two fields that look suspicious to me are:

2013-05-28 00:35:01.960000000

and

19,559999999999999

As for the first one, your other two datetime fields only appear as: 2013-05-28 00:35:01

Thus, it seems odd that one would include a nine-digit fraction of a second.

As for the other, I have no idea what 19,559999999999999, unless it represents:  19.559999999999999

Regardless, I would question if that is what it really represents.

Occasional Contributor
Posts: 5

Re: Importing tab delimited online txt file

Hi again

The first field it's a "concatenate" of other columns. I just need to import it.

The other it's a money value. So I will only consider 2 decimals.

Thanks

Regular Contributor
Posts: 244

Re: Importing tab delimited online txt file

I would assume 19,55999999999999 is just an imprecise value from excel - I've seen that a lot, one of those where two numbers with repeating decimals in binary were added together to get another repeating decimal.

Ask a Question
Discussion stats
  • 9 replies
  • 307 views
  • 6 likes
  • 4 in conversation