Date: Tue, 20 May 2008 11:39:24 -0700
Reply-To: "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Subject: Re: Variation between dates
In-Reply-To: <941871A13165C2418EC144ACB212BDB04E15C9@dshsmxoly1504g.dshs.wa.lcl>
Content-Type: text/plain; charset=iso-8859-1
I probably should have mentioned that because of the conditional lagging, the value for diff is incorrect for every second record for a given CODTIT value. See for example obs=17 in the output below where the value for diff should be -.009, not 2.747
Dan
Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA 98504-5204
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Nordlund, Dan (DSHS/RDA)
> Sent: Tuesday, May 20, 2008 10:59 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Variation between dates
>
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> > Behalf Of Nanita
> > Sent: Tuesday, May 20, 2008 10:21 AM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: Variation between dates
> >
> > On 20 Maio, 17:35, Nord...@DSHS.WA.GOV ("Nordlund, Dan (DSHS/RDA)")
> > wrote:
> > > > -----Original Message-----
> > > > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On
> > > > Behalf Of Nanita
> > > > Sent: Tuesday, May 20, 2008 7:38 AM
> > > > To: SA...@LISTSERV.UGA.EDU
> > > > Subject: Re: Variation between dates
> > >
> > > > On 20 Maio, 15:19, karma <dorjeta...@googlemail.com> wrote:
> > > > > On 20 May, 14:57, Nanita <susana.urb...@gmail.com> wrote:
> > >
> > > > > > Hi there,
> > >
> > > > > > I need to calculate the variation in "VALOR" between
> > dates (1 day
> > > > > > diference). How can I do that?
> > >
> > > > > > Thanks.
> > >
> > > > > > CODFONTE CODTIT ULTDATA MOEDA BOLSA CODITEM
> > > > VALOR DTALT
> > > > > > 1 100019900833 20080417 EUR
> > > > LUXEMBOURG Preco 15H30 96.224 17-04-2008
> > > > > > 1 100019900833 20080418 EUR
> > > > LUXEMBOURG Preco 15H30 96.249 18-04-2008
> > > > > > 1 100019900833 20080421 EUR
> > > > LUXEMBOURG Preco 15H30 95.764 21-04-2008
> > > > > > 1 100019900833 20080422 EUR
> > > > LUXEMBOURG Preco 15H30 96.256 22-04-2008
> > > > > > 1 100019900833 20080423 EUR
> > > > LUXEMBOURG Preco 15H30 96.367 23-04-2008
> > > > > > 1 100019900833 20080424 EUR
> > > > LUXEMBOURG Preco 15H30 96.366 24-04-2008
> > > > > > 1 100019900833 20080425 EUR
> > > > LUXEMBOURG Preco 15H30 95.562 25-04-2008
> > > > > > 1 100019900833 20080428 EUR
> > > > LUXEMBOURG Preco 15H30 96.416 28-04-2008
> > > > > > 1 100019900833 20080429 EUR
> > > > LUXEMBOURG Preco 15H30 96.843 29-04-2008
> > > > > > 1 100019900833 20080430 EUR
> > > > LUXEMBOURG Preco 15H30 96.848 01-05-2008
> > > > > > 1 100019900833 20080502 EUR
> > > > LUXEMBOURG Preco 15H30 96.851 02-05-2008
> > > > > > 1 100019900833 20080505 EUR
> > > > LUXEMBOURG Preco 15H30 96.855 05-05-2008
> > > > > > 1 100019900833 20080506 EUR
> > > > LUXEMBOURG Preco 15H30 96.857 06-05-2008
> > > > > > 1 100019900833 20080507 EUR
> > > > LUXEMBOURG Preco 15H30 96.887 07-05-2008
> > > > > > 1 100019900833 20080508 EUR
> > > > LUXEMBOURG Preco 15H30 96.869 08-05-2008
> > > > > > 1 100056520533 20080104 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.625
> > > > > > 04-01-2008
> > > > > > 1 100056520533 20080107 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.616
> > > > > > 08-01-2008
> > > > > > 1 100056520533 20080109 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.633
> > > > > > 09-01-2008
> > > > > > 1 100056520533 20080110 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.64
> > > > > > 11-01-2008
> > > > > > 1 100056520533 20080111 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.634
> > > > > > 14-01-2008
> > > > > > 1 100056520533 20080115 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.676
> > > > > > 15-01-2008
> > > > > > 1 100056520533 20080116 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.661
> > > > > > 16-01-2008
> > > > > > 1 100056520533 20080117 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.671
> > > > > > 17-01-2008
> > > > > > 1 100056520533 20080118 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.702
> > > > > > 18-01-2008
> > > > > > 1 100056520533 20080121 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.708
> > > > > > 21-01-2008
> > > > > > 1 100056520533 20080122 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.719
> > > > > > 22-01-2008
> > > > > > 1 100056520533 20080123 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.701
> > > > > > 24-01-2008
> > > > > > 1 100056520533 20080125 EUR
> > > > EURONEXT-LISBON Preco 15H30 99.691
> > > > > > 25-01-2008
> > >
> > > > > you could use the lag function:
> > >
> > > > > data testt;
> > > > > set values;
> > > > > diff=valor-lag(valor);
> > > > > run;
> > >
> > > > Thanks for your answer karma. It would help I I had just
> > one COD_TIT
> > > > and the the the dates were sorted. But it isn't the case. Thanks
> > > > anyway. :)
> > >
> > > As has been mentioned before on this list, a "picture" is
> > worth a 1000 words. If you presented a small number of
> > records that illustrated cases of interest, and then
> > presented what the output should look like after processing,
> > you might get more useful advice. I know that it is not
> > clear to me what you want as output.
> > >
> > > Sorry I can't be of more help at this time,
> > >
> > > Dan
> > >
> > > Daniel J. Nordlund
> > > Washington State Department of Social and Health Services
> > > Planning, Performance, and Accountability
> > > Research and Data Analysis Division
> > > Olympia, WA 98504-5204
> >
> > I've tried this code:
> >
> > proc sort data=mylib.TAcc_Obri;
> > by codfonte codtit dtalt;
> > run;
> >
> >
> > data mylib.TAcc_Obriga;
> > set mylib.TAcc_Obri;
> > by codfonte codtit dtalt;
> >
> > if first.codtit then diff=0;
> > if not first.codtit then diff = valor - lag(valor);
> > run;
> >
> > It seems to be working but I don't understand why in line 2
> the value
> > of diff is '.'.
> >
> > Where did I fail?
> >
> >
> > Obs CODTIT VALOR DTALT ISIN
> > diff
> >
> > 1 100019900833 96.224 17APR2008
> > XS0195487912 0.000
> > 2 100019900833 96.249 18APR2008
> > XS0195487912 .
> > 3 100019900833 95.764 21APR2008
> > XS0195487912 -0.485
> > 4 100019900833 96.256 22APR2008
> > XS0195487912 0.492
> > 5 100019900833 96.367 23APR2008
> > XS0195487912 0.111
> > 6 100019900833 96.366 24APR2008
> > XS0195487912 -0.001
> > 7 100019900833 95.562 25APR2008
> > XS0195487912 -0.804
> > 8 100019900833 96.416 28APR2008
> > XS0195487912 0.854
> > 9 100019900833 96.843 29APR2008
> > XS0195487912 0.427
> > 10 100019900833 96.848 01MAY2008
> > XS0195487912 0.005
> > 11 100019900833 96.851 02MAY2008
> > XS0195487912 0.003
> > 12 100019900833 96.855 05MAY2008
> > XS0195487912 0.004
> > 13 100019900833 96.857 06MAY2008
> > XS0195487912 0.002
> > 14 100019900833 96.887 07MAY2008
> > XS0195487912 0.030
> > 15 100019900833 96.869 08MAY2008
> > XS0195487912 -0.018
> > 16 100056520533 99.625 04JAN2008
> > PTSEMCOE0006 0.000
> > 17 100056520533 99.616 08JAN2008
> > PTSEMCOE0006 2.747
> > 18 100056520533 99.633 09JAN2008
> > PTSEMCOE0006 0.017
> > 19 100056520533 99.64 11JAN2008
> > PTSEMCOE0006 0.007
> > 20 100056520533 99.634 14JAN2008
> > PTSEMCOE0006 -0.006
> > 21 100056520533 99.676 15JAN2008
> > PTSEMCOE0006 0.042
> > 22 100056520533 99.661 16JAN2008
> > PTSEMCOE0006 -0.015
> > 23 100056520533 99.671 17JAN2008
> > PTSEMCOE0006 0.010
> > 24 100056520533 99.702 18JAN2008
> > PTSEMCOE0006 0.031
> > 25 100056520533 99.708 21JAN2008
> > PTSEMCOE0006 0.006
> > 26 100056520533 99.719 22JAN2008
> > PTSEMCOE0006 0.011
> > 27 100056520533 99.701 24JAN2008
> > PTSEMCOE0006 -0.018
> > 28 100056520533 99.691 25JAN2008
> > PTSEMCOE0006 -0.010
> > 29 100056520533 99.692 28JAN2008
> > PTSEMCOE0006 0.001
> > 30 100056520533 99.692 29JAN2008
> > PTSEMCOE0006 0.000
> > 31 100056520533 99.686 30JAN2008
> > PTSEMCOE0006 -0.006
> > 32 100056520533 99.686 06FEB2008
> > PTSEMCOE0006 0.000
> > 33 100056520533 99.689 07FEB2008
> > PTSEMCOE0006 0.003
> > 34 100056520533 99.691 08FEB2008
> > PTSEMCOE0006 0.002
> > 35 100056520533 99.688 11FEB2008
> > PTSEMCOE0006 -0.003
> >
> >
>
> The reason you are getting a missing value is that you are
> executing the LAG() conditionally. There have been quite a
> few discussions on the list about why that causes problems.
> You could search the SAS-L archives
> (http://listserv.uga.edu/archives/sas-l.html) for LAG and
> find those discussions. For your program, try this:
>
> data mylib.TAcc_Obriga;
> set mylib.TAcc_Obri;
> by codfonte codtit dtalt;
>
> diff = valor - lag(valor);
> if first.codtit then diff=0;
> run;
>
> Hope this is helpful,
>
> Dan
>
> Daniel J. Nordlund
> Washington State Department of Social and Health Services
> Planning, Performance, and Accountability
> Research and Data Analysis Division
> Olympia, WA 98504-5204
>
>
| 
