topic Re: Conditional lag in SAS Procedures

Conditional lag

Kiteulf — Tue, 19 May 2015 13:31:18 GMT

I have this type of data;

ObjectPrice	Regnr	depreciation	Counter	Primary	opprinpris	transaksjonstid	est_price
2237500,00	AE28404	0,000416667	1	1	2237500
1600000,00	AE28404	0,000277667	2	0	2237500	1096	1556578,033
1100000,00	AE30864	0,000277667	1	1	1100000
800000,00	AR99993	0,000277667	1	1	800000
795000,00	AR99993	0,000277667	2	0	800000	398	711590,9333
799000,00	AR99993	0,000277667	3	0	800000	32	...

I want

IF counter = 3 then transaksjonstid= lag (transaksjonstid)+(transaksjonstid)

else if counter =4 then transaksjonstid = lag2(transaksjonstid)+lag(transaksjonstid) +transaksjonstid

etc...

Re: Conditional lag

Ksharp — Tue, 19 May 2015 13:48:03 GMT

Code not tested.

lag_transaksjonstid=lag (transaksjonstid);

lag_transaksjonstid2=lag 2(transaksjonstid);

IF counter = 3 then transaksjonstid=lag_transaksjonstid+transaksjonstid;

else if counter =4 then transaksjonstid = lag_transaksjonstid2+lag_transaksjonstid +transaksjonstid;

Re: Conditional lag

RW9 — Tue, 19 May 2015 13:48:46 GMT

Not entirely sure on your logic, but it sounds like a join or subquery (possibly hash) would be easier:

proc sql;

create table WANT as

select A.*,

(select sum(TRANSAKSJONSTID) from HAVE where REGNR=A.REGNR and (A.COUNTER-COUNTER) <= COUNTER <= A.COUNTER))

as TOT

from HAVE A;

quit;

Re: Conditional lag

Kurt_Bremser — Tue, 19 May 2015 13:56:09 GMT

Do not call the lag() function conditionally, as it feeds its queue only when called. If you don't call it for N records, the first call at N+1 will still yield the initial missing value, instead of the expected value from the observation N!

Re: Conditional lag

Steelers_In_DC — Tue, 19 May 2015 15:24:40 GMT

You shouldn't use lag this way, set up your lag variables and then use those conditionally:

data have;

infile cards dsd dlm=',';

input ObjectPrice Regnr $ depreciation Counter Primary opprinpris transaksjonstid est_price;

cards;

2237500.00,AE28404,0.000416667,1,1,2237500,,

1600000.00,AE28404,0.000277667,2,0,2237500,1096,1556578.033

1100000.00,AE30864,0.000277667,1,1,1100000,,

800000.00,AR99993,0.000277667,1,1,800000,,

795000.00,AR99993,0.000277667,2,0,800000,398,711590.9333

799000.00,AR99993,0.000277667,3,0,800000,32,

;

run;

data want;

set have;

l1=lag(transaksjonstid);

l2=lag2(transaksjonstid);

if counter = 3 then test = transaksjonstid + l1;

else if counter= 4 then test = transaksjonstid + l1 + l2;

run;