|
On Sun, 20 Aug 2006 16:47:57 -0700, ChrisW75 <9Squirrels@GMAIL.COM> wrote:
>Hi Jun,
> You should have added this to the original post so we could see what
>the previous responses were. I remember that I answered this one, what
>was the problem with my earlier response? Where did it fall down? I
>can't find my earlier response, but I think I would have suggested
>using column input then doing post-processing on the file to transpose
>it.
That's exactly what I suggest now, though not earlier. Jun made a big change
in the nature of the problem.
> One thing alot of people miss is that rather than spending a week
>trying to write a program to handle every possible permutation of the
>badly formatted datafile, sometimes it's better to just go back to the
>supplier and ask for it in a better format. Sometimes it's just a
>simple matter to get them to change the extract process or whatever.
This is clearly a report layout. Backing data out of such a file is not only
a nuisance, it often introduces excessive rounding as well.
>ChrisW75
>
>Jun wrote:
>> Dear All:
>>
>> I asked this question two days ago and have recieved several replys.
>> One of them works for the example I provided. But I forgot to provide
>> some details about the table. So I have to ask you help again with a
>> new sample table. Thanks a lot.
It's more than detail. You've fundamentally changed the problem.
>>
>> I have several text files. All these files contains titles and a table.
>> I just want to read in the numbers appeared in the table (not include
>> the number appeared in title and in the table row name and column name)
>> into a SAS dataset. In the SAS dataset, I want to have three columns,
>> the first column indicates at which column a particular number appeared
>> in the old table, the second column indicates at which row a
>> particular number appeared in the old table, the third column is the
>> number. The following is one sample of the text file. Any suggestion
>> will be appreciated.
>> Note: 1.column is like description and it may contain several words.
My earlier solution would handle this as long as there are not consecutive
embedded blanks. Unfortunately the example shows consecutive embedded
blanks. But since I am replacing list input with column input, it's easy to
just ignore the whole field.
>> 2. There are may have some missing value in the table.
That's what really changes things.
>> title
>>
>>
-------------------------------------------------------------------------------------------------
>>
>> column1 ----column2--- ---column3-----
>> Cloumn3
>> n % n %
>>
>>
------------------------------------------------------------------------------------------------
>> A AAA 22 100
>> 0.33
Your example is wrapping. Usually that's a minor flaw, but here it's major.
>> BB B B 33 12.2 1200 23
>> CCCC 55 8.9 42 0.3
>>
>> ---------------------------------------------------------------------------
>>
>> The sas dataset I want to create for the above example is as follows:
>>
>> 1 1 22
>> 1 2 33
>> 1 3 55
>> 2 1 .
>> 2 2 12.2
>> 2 3 8.9
>> 3 1 100
>> 3 2 1200
>> 3 3 42
>> 4 1 .
>> 4 2 23
>> 4 3 0.3
>> 5 1 0.33
>> 5 2 .
>> 5 5 .
>>
>> Thanks,
>> Jun
Try something modeled on this:
data extract;
infile cards truncover;
input @;
if rownum or (lag(_infile_)=:'-' and not missing(_infile_) );
rownum + 1;
if missing(_infile_) then stop;
input number1 11-18
number2 19-26
number3 27-34
number4 35-42;
cards;
title
----------------------------------------
column1 ----column2--- ---column3--
n % n %
-----------------------------------------
AAAA 22 10.2 100
BB BB 33 12.2 1200 23
CCCC 55 8.9 42 0.3
-----------------------------------------
;
Notice that I've made the example narrower. You will have to work out the
correct column specifications for the real data.
The output can be transposed as necessary. There are lots of examples on
that in the archives.
| 
