Solved
Contributor
Posts: 66

# Create variable with a time condition

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

I have this kind of data:

DateCatPrice
01/10/2013A1.1
02/10/2013A1.2

03/10/2013

A1.3
04/10/2013A1.4
05/10/2013A1.5

01/10/2013

B33
02/10/2013B33
03/10/2013B29
04/10/2013B32
05/10/2013B30

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

DateCatPricePrice-2Price-4
01/10/2013A1.1.
02/10/2013A1.2.

03/10/2013

A1.31.1
04/10/2013A1.41.2
05/10/2013A1.51.31.1

01/10/2013

B33
02/10/2013B33
03/10/2013B2933
04/10/2013B3233
05/10/2013B303033

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!

Accepted Solutions
Solution
‎10-23-2013 05:01 PM
Super User
Posts: 23,754

## Re: Create variable with a time condition

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;

All Replies
Solution
‎10-23-2013 05:01 PM
Super User
Posts: 23,754

## Re: Create variable with a time condition

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;

Contributor
Posts: 66

## Re: Create variable with a time condition

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:

Super User
Posts: 23,754

## Re: Create variable with a time condition

Typo..those should be price2/price4 = .

Super User
Posts: 23,754

## Re: Create variable with a time condition

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

Posts: 3,167

## Re: Create variable with a time condition

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

Super User
Posts: 23,754

## Re: Create variable with a time condition

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 6 replies
• 276 views
• 3 likes
• 3 in conversation