topic Re: Create variable with a time condition in SAS Procedures

Create variable with a time condition

fri0 — Wed, 23 Oct 2013 20:41:44 GMT

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!

Re: Create variable with a time condition

Reeza — Wed, 23 Oct 2013 21:01:42 GMT

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;

Re: Create variable with a time condition

Reeza — Wed, 23 Oct 2013 21:04:48 GMT

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

Re: Create variable with a time condition

Haikuo — Wed, 23 Oct 2013 21:12:25 GMT

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

Re: Create variable with a time condition

Reeza — Wed, 23 Oct 2013 21:29:42 GMT

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;

Re: Create variable with a time condition

fri0 — Thu, 24 Oct 2013 19:08:27 GMT

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:

Re: Create variable with a time condition

Reeza — Thu, 24 Oct 2013 19:42:09 GMT

Typo..those should be price2/price4 = .