Prev: SAS optmization
Next: Sort Large Dataset
From: ChrisG on 20 Mar 2010 14:01
Hello everybody

I have an issue related to data extraction and putting it in the right
format
Usually, I can get stock market data in two formats (usually into
excel):

format 1

MF FP Equity DGFP FP Equity 2045Q FP Equity
date PX_last date PX_last date PX_last
05/01/1998 23,9068 05/01/1998 05/01/1998 59,3027
06/01/1998 24,3918 06/01/1998 58,6776
07/01/1998 25,5699 07/01/1998 58,6929
08/01/1998 25,0848 08/01/1998 60,8272
09/01/1998 25,1541 09/01/1998 60,2174
12/01/1998 24,3572 12/01/1998 58,9215
13/01/1998 24,7037 13/01/1998 58,388
14/01/1998 24,9462 14/01/1998 57,1684
15/01/1998 25,7777 15/01/1998 58,6776
16/01/1998 25,9856 16/01/1998 58,6776
19/01/1998 26,8172 19/01/1998 58,5404

format 2

MF FP Equity date 05/01/1998 06/01/1998 07/01/1998 08/01/1998
09/01/1998 12/01/1998 13/01/1998 14/01/1998 15/01/1998 16/01/1998
19/01/1998
PX_last 23,9068 24,3918 25,5699 25,0848 25,1541 24,3572 24,7037
24,9462 25,7777 25,9856 26,8172

DGFP FP Equity date 05/01/1998
PX_last

2045Q FP Equity date 05/01/1998 06/01/1998 07/01/1998 08/01/1998
09/01/1998 12/01/1998 13/01/1998 14/01/1998 15/01/1998 16/01/1998
19/01/1998
PX_last 59,3027 58,6776 58,6929 60,8272 60,2174 58,9215 58,388
57,1684 58,6776 58,6776 58,5404

for instance, MF FP Equity is the ticker, then date of listing and the
px_last is the name for the closing daily price of the stock

now my dream is to get ouf of one of these "raw" format the following
output into sas:

sas format

ticker date PX_last
MF FP Equity 05/01/1998 23,9068
MF FP Equity 06/01/1998 24,3918
MF FP Equity 07/01/1998 25,5699
MF FP Equity 08/01/1998 25,0848
MF FP Equity 09/01/1998 25,1541
MF FP Equity 12/01/1998 24,3572
MF FP Equity 13/01/1998 24,7037
MF FP Equity 14/01/1998 24,9462
MF FP Equity 15/01/1998 25,7777
MF FP Equity 16/01/1998 25,9856
MF FP Equity 19/01/1998 26,8172
DGFP FP Equity 05/01/1998 .
2045Q FP Equity 05/01/1998 59,3027
2045Q FP Equity 06/01/1998 58,6776
2045Q FP Equity 07/01/1998 58,6929
2045Q FP Equity 08/01/1998 60,8272
2045Q FP Equity 09/01/1998 60,2174
2045Q FP Equity 12/01/1998 58,9215
2045Q FP Equity 13/01/1998 58,388
2045Q FP Equity 14/01/1998 57,1684
2045Q FP Equity 15/01/1998 58,6776
2045Q FP Equity 16/01/1998 58,6776
2045Q FP Equity 19/01/1998 58,5404

does anyone have a clue ?

thank you very much in advance
best
CG
From: data _null_; on 20 Mar 2010 14:21
On Mar 20, 1:01 pm, ChrisG <chris.godlew...(a)gmail.com> wrote:
> Hello everybody
>
> I have an issue related to data extraction and putting it in the right
> format
> Usually, I can get stock market data in two formats (usually into
> excel):
>
> format 1
>
> MF      FP Equity                       DGFP    FP Equity                       2045Q   FP Equity
> date    PX_last         date    PX_last         date    PX_last
> 05/01/1998      23,9068         05/01/1998                      05/01/1998      59,3027
> 06/01/1998      24,3918                                 06/01/1998      58,6776
> 07/01/1998      25,5699                                 07/01/1998      58,6929
> 08/01/1998      25,0848                                 08/01/1998      60,8272
> 09/01/1998      25,1541                                 09/01/1998      60,2174
> 12/01/1998      24,3572                                 12/01/1998      58,9215
> 13/01/1998      24,7037                                 13/01/1998      58,388
> 14/01/1998      24,9462                                 14/01/1998      57,1684
> 15/01/1998      25,7777                                 15/01/1998      58,6776
> 16/01/1998      25,9856                                 16/01/1998      58,6776
> 19/01/1998      26,8172                                 19/01/1998      58,5404
>
> format 2
>
> MF      FP Equity       date    05/01/1998      06/01/1998      07/01/1998      08/01/1998
> 09/01/1998      12/01/1998      13/01/1998      14/01/1998      15/01/1998      16/01/1998
> 19/01/1998
>         PX_last 23,9068 24,3918 25,5699 25,0848 25,1541 24,3572 24,7037
> 24,9462 25,7777 25,9856 26,8172
>
> DGFP    FP Equity       date    05/01/1998
>         PX_last
>
> 2045Q   FP Equity       date    05/01/1998      06/01/1998      07/01/1998      08/01/1998
> 09/01/1998      12/01/1998      13/01/1998      14/01/1998      15/01/1998      16/01/1998
> 19/01/1998
>         PX_last 59,3027 58,6776 58,6929 60,8272 60,2174 58,9215 58,388
> 57,1684 58,6776 58,6776 58,5404
>
> for instance, MF FP Equity is the ticker, then date of listing and the
> px_last is the name for the closing daily price of the stock
>
> now my dream is to get ouf of one of these "raw" format the following
> output into sas:
>
> sas format
>
> ticker  date    PX_last
> MF      FP Equity       05/01/1998      23,9068
> MF      FP Equity       06/01/1998      24,3918
> MF      FP Equity       07/01/1998      25,5699
> MF      FP Equity       08/01/1998      25,0848
> MF      FP Equity       09/01/1998      25,1541
> MF      FP Equity       12/01/1998      24,3572
> MF      FP Equity       13/01/1998      24,7037
> MF      FP Equity       14/01/1998      24,9462
> MF      FP Equity       15/01/1998      25,7777
> MF      FP Equity       16/01/1998      25,9856
> MF      FP Equity       19/01/1998      26,8172
> DGFP    FP Equity       05/01/1998      .
> 2045Q   FP Equity       05/01/1998      59,3027
> 2045Q   FP Equity       06/01/1998      58,6776
> 2045Q   FP Equity       07/01/1998      58,6929
> 2045Q   FP Equity       08/01/1998      60,8272
> 2045Q   FP Equity       09/01/1998      60,2174
> 2045Q   FP Equity       12/01/1998      58,9215
> 2045Q   FP Equity       13/01/1998      58,388
> 2045Q   FP Equity       14/01/1998      57,1684
> 2045Q   FP Equity       15/01/1998      58,6776
> 2045Q   FP Equity       16/01/1998      58,6776
> 2045Q   FP Equity       19/01/1998      58,5404
>
> does anyone have a clue ?
>
> thank you very much in advance
> best
> CG

It it too hard to tell what the data in FORMAT1 and FORMAT2 are really
suppose to be. Formatting is mangled. Post these data as CSV files
with varnames in row 1. That way it will be clear what field is what
and the rest.
From: ChrisG on 20 Mar 2010 18:04
On 20 mar, 19:21, "data _null_;" <datan...(a)gmail.com> wrote:
> On Mar 20, 1:01 pm, ChrisG <chris.godlew...(a)gmail.com> wrote:
>
>
>
> > Hello everybody
>
> > I have an issue related to data extraction and putting it in the right
> > format
> > Usually, I can get stock market data in two formats (usually into
> > excel):
>
> > format 1
>
> > MF      FP Equity                       DGFP    FP Equity                       2045Q   FP Equity
> > date    PX_last         date    PX_last         date    PX_last
> > 05/01/1998      23,9068         05/01/1998                      05/01/1998      59,3027
> > 06/01/1998      24,3918                                 06/01/1998      58,6776
> > 07/01/1998      25,5699                                 07/01/1998      58,6929
> > 08/01/1998      25,0848                                 08/01/1998      60,8272
> > 09/01/1998      25,1541                                 09/01/1998      60,2174
> > 12/01/1998      24,3572                                 12/01/1998      58,9215
> > 13/01/1998      24,7037                                 13/01/1998      58,388
> > 14/01/1998      24,9462                                 14/01/1998      57,1684
> > 15/01/1998      25,7777                                 15/01/1998      58,6776
> > 16/01/1998      25,9856                                 16/01/1998      58,6776
> > 19/01/1998      26,8172                                 19/01/1998      58,5404
>
> > format 2
>
> > MF      FP Equity       date    05/01/1998      06/01/1998      07/01/1998      08/01/1998
> > 09/01/1998      12/01/1998      13/01/1998      14/01/1998      15/01/1998      16/01/1998
> > 19/01/1998
> >         PX_last 23,9068 24,3918 25,5699 25,0848 25,1541 24,3572 24,7037
> > 24,9462 25,7777 25,9856 26,8172
>
> > DGFP    FP Equity       date    05/01/1998
> >         PX_last
>
> > 2045Q   FP Equity       date    05/01/1998      06/01/1998      07/01/1998      08/01/1998
> > 09/01/1998      12/01/1998      13/01/1998      14/01/1998      15/01/1998      16/01/1998
> > 19/01/1998
> >         PX_last 59,3027 58,6776 58,6929 60,8272 60,2174 58,9215 58,388
> > 57,1684 58,6776 58,6776 58,5404
>
> > for instance, MF FP Equity is the ticker, then date of listing and the
> > px_last is the name for the closing daily price of the stock
>
> > now my dream is to get ouf of one of these "raw" format the following
> > output into sas:
>
> > sas format
>
> > ticker  date    PX_last
> > MF      FP Equity       05/01/1998      23,9068
> > MF      FP Equity       06/01/1998      24,3918
> > MF      FP Equity       07/01/1998      25,5699
> > MF      FP Equity       08/01/1998      25,0848
> > MF      FP Equity       09/01/1998      25,1541
> > MF      FP Equity       12/01/1998      24,3572
> > MF      FP Equity       13/01/1998      24,7037
> > MF      FP Equity       14/01/1998      24,9462
> > MF      FP Equity       15/01/1998      25,7777
> > MF      FP Equity       16/01/1998      25,9856
> > MF      FP Equity       19/01/1998      26,8172
> > DGFP    FP Equity       05/01/1998      .
> > 2045Q   FP Equity       05/01/1998      59,3027
> > 2045Q   FP Equity       06/01/1998      58,6776
> > 2045Q   FP Equity       07/01/1998      58,6929
> > 2045Q   FP Equity       08/01/1998      60,8272
> > 2045Q   FP Equity       09/01/1998      60,2174
> > 2045Q   FP Equity       12/01/1998      58,9215
> > 2045Q   FP Equity       13/01/1998      58,388
> > 2045Q   FP Equity       14/01/1998      57,1684
> > 2045Q   FP Equity       15/01/1998      58,6776
> > 2045Q   FP Equity       16/01/1998      58,6776
> > 2045Q   FP Equity       19/01/1998      58,5404
>
> > does anyone have a clue ?
>
> > thank you very much in advance
> > best
> > CG
>
> It it too hard to tell what the data in FORMAT1 and FORMAT2 are really
> suppose to be.  Formatting is mangled.  Post these data as CSV files
> with varnames in row 1.  That way it will be clear what field is what
> and the rest.

FORMAT1 & FORMAT 2 are what i can get from the data server into an xls
file
i have saved as .csv and i get basically the same stuff:
there are no varnames when i get the data from the server

FORMAT1
here the data comes in column for each ticker (for instance MF FP)
there are 2 columns: one with the date (daily) and the other with PX-
last = closing price of the stock

MF FP Equity DGFP FP Equity 2045Q FP Equity
date PX_last date PX_last date PX_last
05/01/1998 23,9068 05/01/1998 05/01/1998 59,3027
06/01/1998 24,3918 06/01/1998 58,6776
07/01/1998 25,5699 07/01/1998 58,6929
08/01/1998 25,0848 08/01/1998 60,8272
09/01/1998 25,1541 09/01/1998 60,2174
12/01/1998 24,3572 12/01/1998 58,9215
13/01/1998 24,7037 13/01/1998 58,388
14/01/1998 24,9462 14/01/1998 57,1684
15/01/1998 25,7777 15/01/1998 58,6776
16/01/1998 25,9856 16/01/1998 58,6776
19/01/1998 26,8172 19/01/1998 58,5404

FORMAT 2
here the data comes in lines
same explanations apply as for format 1

MF FP Equity date 05/01/1998 06/01/1998 07/01/1998 08/01/1998
09/01/1998 12/01/1998 13/01/1998 14/01/1998 15/01/1998 16/01/1998
19/01/1998
PX_last 23,9068 24,3918 25,5699 25,0848 25,1541 24,3572 24,7037
24,9462 25,7777 25,9856 26,8172

DGFP FP Equity date 05/01/1998
PX_last

2045Q FP Equity date 05/01/1998 06/01/1998 07/01/1998 08/01/1998
09/01/1998 12/01/1998 13/01/1998 14/01/1998 15/01/1998 16/01/1998
19/01/1998
PX_last 59,3027 58,6776 58,6929 60,8272 60,2174 58,9215 58,388
57,1684 58,6776 58,6776 58,5404

my dream is format 3
where things are perfect for SAS

ticker date PX_last
MF FP Equity 05/01/1998 23,9068
MF FP Equity 06/01/1998 24,3918
MF FP Equity 07/01/1998 25,5699
MF FP Equity 08/01/1998 25,0848
MF FP Equity 09/01/1998 25,1541
MF FP Equity 12/01/1998 24,3572
MF FP Equity 13/01/1998 24,7037
MF FP Equity 14/01/1998 24,9462
MF FP Equity 15/01/1998 25,7777
MF FP Equity 16/01/1998 25,9856
MF FP Equity 19/01/1998 26,8172
DGFP FP Equity 05/01/1998 .
2045Q FP Equity 05/01/1998 59,3027
2045Q FP Equity 06/01/1998 58,6776
2045Q FP Equity 07/01/1998 58,6929
2045Q FP Equity 08/01/1998 60,8272
2045Q FP Equity 09/01/1998 60,2174
2045Q FP Equity 12/01/1998 58,9215
2045Q FP Equity 13/01/1998 58,388
2045Q FP Equity 14/01/1998 57,1684
2045Q FP Equity 15/01/1998 58,6776
2045Q FP Equity 16/01/1998 58,6776
2045Q FP Equity 19/01/1998 58,5404

var1 = TICKER
var2 = date
var3= price

CG
 | 
Pages: 1
Prev: SAS optmization
Next: Sort Large Dataset