Create variable with a time condition

[fri0]

Hi, I hope you could help me with my problem.

I have this kind of data:

Date	Cat	Price
01/10/2013	A	1.1
02/10/2013	A	1.2
03/10/2013	A	1.3
04/10/2013	A	1.4
05/10/2013	A	1.5
01/10/2013	B	33
02/10/2013	B	33
03/10/2013	B	29
04/10/2013	B	32
05/10/2013	B	30

I need transform the data to have when it would be possible the price of the each category 2 and 4 days before:

Date	Cat	Price	Price-2	Price-4
01/10/2013	A	1.1	.
02/10/2013	A	1.2	.
03/10/2013	A	1.3	1.1
04/10/2013	A	1.4	1.2
05/10/2013	A	1.5	1.3	1.1
01/10/2013	B	33
02/10/2013	B	33
03/10/2013	B	29	33
04/10/2013	B	32	33
05/10/2013	B	30	30	33

I was trying  with this:

PROC SQL;

CREATE TABLE WORK.OUTPUT AS SELECT

A.DATE,

A.CAT,

A.PRICE AS PRICE,

(SELECT B.PRICE FROM WORK.COT_LAST AS B

WHERE DATEPART(B.DATE) = INTNX('workday',DATEPART(A.DATE),-2,'same')AND A.CAT=B.CAT) AS PRICE2

(SELECT B.PRICE FROM WORK.COT_LAST AS B

WHERE DATEPART(B.DATE) = INTNX('workday',DATEPART(A.DATE),-4,'same')AND A.CAT=B.CAT) AS PRICE2

FROM WORK.PREV AS A;

QUIT;

Any idea using sql or data? Thanks in advance!

[Reeza · Accepted solution]

lag2 and lag4 in a data step, with a reset when you hit a new cat and until you hit the appropriate increment.

data want;

set have;

by cat;

retain count;

if first.cat then count=1;

price2=lag2(price);

price4=lag4(price);

if count<3 then lag2=.;

if count<5 then lag4=.;

run;

[fri0]

Thanks! It works perfectly!, could your explain me this -> lag2=.

I thought that you could make missing variables, but lag2 is a function, right? :smileyconfused:

[Reeza]

Typo..those should be price2/price4 = .

[Reeza]

or if you have ETS licensed use proc expand:

proc expand data=haveout=want method=none;

     by cat;

  id date;

  convert price = price_lag2 / transformout=(lag 2);

  convert price = price_lag4 / transformout=(lag 4);

  run;

SAS/ETS(R) 9.2 User's Guide

[Haikuo]

Hi, Just like Reeza has pointed out, there are quick and dirty ways of doing it if your data is:

1) sorted or clustered by 'Cat'

2)No skipping in dates, meaning counting rows is equivalent of counting days.

However, if not, you would need more arsenal (such as Hash  in my example) to do the job:

data have;

input Date:ddmmyy10.    Cat   $ Price;

format date ddmmyy10;

cards;

01/10/2013  A     1.1

02/10/2013  A     1.2

03/10/2013  A     1.3

04/10/2013  A     1.4

05/10/2013  A     1.5

01/10/2013  B     33

02/10/2013  B     33

03/10/2013  B     29

04/10/2013  B     32

05/10/2013  B     30

;

data want_qick_dirty;

  do _n_=1 by 1 until (last.cat);

    set have;

        by cat notsorted;

price_2=ifn(_n_<=2,.,lag2(price));

price_4=ifn(_n_<=4,.,lag4(price));

        output;

end;

run;

/*Hash*/

data want_hash;

  if _n_=1 then do;

    if 0 then set have (rename=price=_price);

      declare hash h(dataset: 'have(rename=price=_price)');

      h.definekey('cat','date');

      h.definedata('_price');

      h.definedone();

  end;

  set have;

    price_2=ifn(h.find(key:cat, key:date-2)=0, _price,.);

      price_4=ifn(h.find(key:cat,key:date-4)=0,_price,.);

      drop _price;

run;

Good Luck,

Haikuo

[Reeza]

Assuming the dates matter the following is a SQL solution...your data shows dates, not datetime variables though so not sure why your using the datepart function.

If you actually have datetime variables wrap each of the dates below in a datepart function.  ie datepart(a.date)=datepart(b.date)+2

proc sql;

    create table want as

    select a.*, b.price as price2, c.price as price4

    from have as a

    left join have as b

    on a.date=b.date+2 and a.cat=b.cat

    left join have as c

    on a.date=c.date+4 and a.cat=c.cat

    order by cat, date;

quit;