how to properly read a lst file

[alepage]

data mydata; 
  infile "/.../test/wu.valide.auto.prm.lst"  FIRSTOBS=6;
   input obs rsp $ risk $ line $ cie $ Nb_transactions prime;
run;

I am not setting properly the options.

Does someone better than me can help.

                                                                                                                                     
PREMIUMS VALIDATION  - TEST                                                                                                
                                                                                                                                     
                                               NB                                                                                    
Obs      RSP       RISK    LINE    CIE    TRANSACTIONS      PRIME                                                                    
                                                                                                                                     
  1                                          1843858       85379040                                                                  
                                                                                                                                     
  2                                 3        1843858       85379040                                                                  
                                                                                                                                     
  3                        AUTO              1843858       85379040                                                                  
                                                                                                                                     
  4                        AUTO     3        1843858       85379040                                                                  
                                                                                                                                     
  5                COMM                       322475       14702635                                                                  
                                                                                                                                     
  6                PERS                      1521383       70676405                                                                  
                                                                                                                                     
  7                COMM             3         322475       14702635                                                                  
                                                                                                                                     
  8                PERS             3        1521383       70676405                                                                  
                                                                                                                                     
  9                COMM    AUTO               322475       14702635                                                                  
                                                                                                                                     
 10                PERS    AUTO              1521383       70676405                                                                  
                                                                                                                                     
 11                COMM    AUTO     3         322475       14702635                                                                  
                                                                                                                                     
 12                PERS    AUTO     3        1521383       70676405                                                                  
                                                                                                                                     
 13    DIRECT                                1777709       86858816                                                                  
                                                                                                                                     
 14    RSPCEDED                                66149       -1479776                                                                  
                                                                                                                                     
 15    DIRECT                       3        1777709       86858816                                                                  
                                                                                                                                     
 16    RSPCEDED                     3          66149       -1479776                                                                  
                                                                                                                                     
 17    DIRECT              AUTO              1777709       86858816                                                                  
                                                                                                                                     
 18    RSPCEDED            AUTO                66149       -1479776                                                                  
                                                                                                                                     
 19    DIRECT              AUTO     3        1777709       86858816                                                                  
                                                                                                                                     
 20    RSPCEDED            AUTO     3          66149       -1479776                                                                  
                                                                                                                                     
 21    DIRECT      COMM                       322475       14702635                                                                  
                                                                                                                                     
 22    DIRECT      PERS                      1455234       72156181                                                                  
                                                                                                                                     
 23    RSPCEDED    PERS                        66149       -1479776                                                                  
                                                                                                                                     
 24    DIRECT      COMM             3         322475       14702635                                                                  
                                                                                                                                     
 25    DIRECT      PERS             3        1455234       72156181                                                                  
                                                                                                                                     
 26    RSPCEDED    PERS             3          66149       -1479776                                                                  
                                                                                                                                     
 27    DIRECT      COMM    AUTO               322475       14702635                                                                  
                                                                                                                                     
 28    DIRECT      PERS    AUTO              1455234       72156181                                                                  
                                                                                                                                     
 29    RSPCEDED    PERS    AUTO                66149       -1479776                                                                  
                                                                                                                                     
 30    DIRECT      COMM    AUTO     3         322475       14702635                                                                  
                                                                                                                                     
 31    DIRECT      PERS    AUTO     3        1455234       72156181                                                                  
                                                                                                                                     
 32    RSPCEDED    PERS    AUTO     3          66149       -1479776                                                                  

[PaigeMiller]

I am not setting properly the options.

Explain this. Why do you think this? If you are getting errors, please show us the LOG. If you are getting the wrong results, show us the incorrect results and explain what you think the correct results are. Please don't make us guess, please don't make us re-do the work you have already done.

[alepage]

if you try to read the txt or lst file below with the above mentioned code, the file is not read properly

here's how it is read:

obs rsp risk line cie Nb_transactions prime
. RSP RISK LINE CIE . .
1 1843858 85379040 2 3 1843858 85379040
3 AUTO 1843858 85379040 4 . 3
5 COMM 322475 14702635 6 . 1521383
7 COMM 3 322475 14702635 8 .
9 COMM AUTO 322475 14702635 10 .
11 COMM AUTO 3 322475 14702635 12
13 DIRECT 1777709 86858816 14 . 66149
15 DIRECT 3 1777709 86858816 16 .
17 DIRECT AUTO 1777709 86858816 18 .
19 DIRECT AUTO 3 1777709 86858816 20
21 DIRECT COMM 322475 14702635 22 .
23 RSPCEDED PERS 66149 -1479776 24 .
25 DIRECT PERS 3 1455234 72156181 26
27 DIRECT COMM AUTO 322475 14702635 28
29 RSPCEDED PERS AUTO 66149 -1479776 30
31 DIRECT PERS AUTO 3 1455234 72156181
32 RSPCEDED PERS AUTO 3 66149 -1479776

[donricardo · Accepted solution]

Hi - if you could supply a file layout of what this raw data set contains, that would be very helpful.   Not sure if you want to read in the obs number, but i left it in for you here.   Because some of your observations contain spaces which is the default delimiter,  you have two options:  1) those values should be quoted so as not to confuse with the space delimiter; or 2) use the & input modifier to permit embedded single blanks.   I do believe the 'real' delimiter then must contain 2 blanks.  This is called "Modified List Input."     Just to perhaps get you started, have a look.   Here, I've quoted those fields with embedded blanks.

data insurance;
infile datalines dlm=' ' dsd;
input obs : $2. rsp : $quote20. risk : 10.;
format risk dollar10.;
datalines;
1 ' ' 1843858 
3 'AUTO' 1843858 
5 'COMM' 322475 
11 'COMM AUTO' 322475
;

yields this log:

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         data insurance;
29         infile datalines dlm=' ' dsd;
30         input obs : $2. rsp : $quote20. risk : 10.;
31         format risk dollar10.;
32         datalines;

NOTE: The data set WORK.INSURANCE has 4 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

And this result:

and this new SAS table:

Option #2, using the & input modifier, allowing embedded blanks:

data insurance;
infile datalines dlm=' ' dsd;
input obs $2. rsp& $20. risk 10.;
format risk dollar10.;
datalines;
1 ' '  1843858 
3 AUTO  1843858 
5 COMM  322475 
11 COMM AUTO  322475
;
title "using & input modifer";
proc print noobs;
run;

With this result:  

To get more information, look under "input modifiers" in SAS doc or just google it.

I hope this gets you on your way - this can be a tricky subject!

[ballardw]

If these LST files are generated by SAS as output then perhaps you would be better off directing the output to a data set when created. It might help to show the code that generates the LST file.

[PaigeMiller]

Agreeing with @ballardw 

Don't write to a .LST file when you can write to a SAS data set.

[Tom]

What do you mean by LST file?  Are you trying to read back in something that was printed by SAS to the standard output (what they now call the LISTING destination)?

If so what you shared does not look right to me.  I would expect the output of something like PROC PRINT to be aligned into columns. (But NOTE that the alignment positions might change for each page of the output as the lengths of that values on that page might be different.)

However your text as presented does have 7 values per line. So your program should work fine.  The resulting dataset will look just as messy as the text you shared.

Normally when reading a real text file you need to worry about reading past the end of the line when the last value(s) on the line are empty.  To prevent that add the TRUNCOVER option to the INFILE statement.

If you are complaining that it seems to have read more words from some of the lines that you can see then I suspect that your actual file might have invisible characters that are not spaces that INPUT is treating as words/fields on the line.  Perhaps TAB ('09'x) characters or NON-BREAKING space ('A0'x) characters.

[Tom]

So that looks more like a listing now.  The values are in fixed positions on the line, so adjust your INPUT statement to match to locations.  Not sure where they are?  Use the LIST statement.

266  data mydata;
267   input;
268   list;
269  cards;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
270                                                       NB
271        Obs      RSP       RISK    LINE    CIE    TRANSACTIONS      PRIME
272
273          1                                          1843858       85379040
274
275          2                                 3        1843858       85379040
276
277          3                        AUTO              1843858       85379040
278
279          4                        AUTO     3        1843858       85379040
280
281          5                COMM                       322475       14702635
282
283          6                PERS                      1521383       70676405
284
285          7                COMM             3         322475       14702635
286
287          8                PERS             3        1521383       70676405
288

So you just need an input statement that using column ranges.

data mydata; 
   infile "/.../test/wu.valide.auto.prm.lst"  FIRSTOBS=6;
   input obs 1-5 rsp $ 10-15 ...
run;

But watch out if the listing is more than one page long as PROC PRINT will normally re-align the widths of the columns when the lengths of the values on the page is different.