topic Re: Conditional lag in SAS Programming

Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

Re: Conditional lag

JoMa — Sun, 14 Feb 2016 11:54:38 GMT

Hi there,

I would need your help in restructuring a database based on a time series.

I have a database similar to this one (row-values):

Row	UserID	Category	Day	Values	PosVal(d-1)	PosVal(d-2)	PosVal(d-3)
1	970	1	1	3	0	0	0
2	444	1	1	4	0	0	0
3	333	1	1	5	0	0	0
4	234	1	1	1	0	0	0
5	970	1	2	2	0	0	0
6	555	1	2	3	0	0	0
7	444	1	2	42	4	0	0
8	456	1	2	7	0	0	0
9	970	1	3	5	2	0	0
10	333	1	3	31	0	5	0
11	123	1	3	4	0	0	0
12	321	1	3	63	0	0	0
13	6557	1	3	2	0	0	0
14	970	1	4	4	5	2	0
15	234	1	4	5	0	0	1
16	213	1	4	3	0	0	0
17	456	1	4	2	0	7	0
18	233	1	4	1	0	0	0
19	444	2	1	4	0	0	0
20	333	2	1	63	0	0	0
21	444	2	2	2	4	0	0
22	654	2	2	4	0	0	0
23	444	2	3	7	2	4	0

And PosVal(d-1), PosVal(d-2) and PosVal(d-3) are the variables that I need to create.

Basically these are conditional lags of the “values” depending on the userid, category and day where d-1 stands for the first lag, d-2 2 lags etc. I believe that would require loops but I am very much unable to create these lags… I’d very much appreciate your help!

Thanks you

LinusH — Sun, 14 Feb 2016 12:46:42 GMT

It's unclear to me how the lagged values are calculated.
Also, try to get started with a program. Even if it doesn't work it might help us understand what you are trying to do.

JoMa — Sun, 14 Feb 2016 13:20:41 GMT

I'll try to explain it by userid=444.

Userid=444 has the following values (from the above table):

Values is a column and the content of it should be lagged.

PosVal(d-1)

is the content of “ Values” from the day before (d-1) of userid X in category X
If there is no row of UserIDX on the previous day then PosVal(d-1)=0
- Example row 1: PosVal(d-1) = content of “ Values” of userid444 on day=0 in category 1 -> day 0 does not exist, hence=0
- Example row2= PostVal(d-1) content of “ Values” of userid444 on day=1 (day before day 2) in category 1 is 4;

PosVal(d-2)

Is the content of “ Values” from two days before (d-2) of userid 444 in category
If there is no row of UserIDx two days before then PosVal(d-2)=0
- Example row 1: PosVal(d-2) = content of “ Values” of userid444 on day= -1 in category 1 -> day -1 does not exist, hence=0
- Example row 5: PosVal(d-2) = content of “ Values” of userid444 on day= 1 (two days before day3) in category1 is 4

I am pretty much stuck in this misery...and appreciate any further help....

Astounding — Sun, 14 Feb 2016 13:33:22 GMT

You will need a three-step approach. Here's an overview.

The first step is already done in your second example: sort the data set by UserID Category.

Step 2: Compute the lags every time.

PosVal_d1 = lag(value);

PosVal_d2 = lag2(value);

Step 3: Re-set the assigned values of PosVal_d1 and PosVal_d2 to 0 when appropriate. This means adding a BY statement (BY UserID Category) to create some of the tools that IF/THEN statements will require to reset the calculated variables. If this becomes complex, we can always re-visit the final parts of the logic.

Reeza — Sun, 14 Feb 2016 13:40:00 GMT

Have you looked into proc expand? Look at the last example and your BY groups would be userid and category.

JoMa — Sun, 14 Feb 2016 16:37:55 GMT

oh my! it was as simple as that. Thanks for your help

Reeza — Sun, 14 Feb 2016 17:13:26 GMT

Please mark the question as answered if you have a solution.

data_null__ — Sun, 14 Feb 2016 17:25:59 GMT

data lags;
   infile cards expandtabs firstobs=2;
   input row userid:$4. cat day value l1-l3;
   drop row;
   cards;
Row	UserID	Category	Day	Values	PosVal(d-1)	PosVal(d-2)	PosVal(d-3)
1	970	1	1	3	0	0	0
2	444	1	1	4	0	0	0
3	333	1	1	5	0	0	0
4	234	1	1	1	0	0	0
5	970	1	2	2	0	0	0
6	555	1	2	3	0	0	0
7	444	1	2	42	4	0	0
8	456	1	2	7	0	0	0
9	970	1	3	5	2	0	0
10	333	1	3	31	0	5	0
11	123	1	3	4	0	0	0
12	321	1	3	63	0	0	0
13	6557	1	3	2	0	0	0
14	970	1	4	4	5	2	0
15	234	1	4	5	0	0	1
16	213	1	4	3	0	0	0
17	456	1	4	2	0	7	0
18	233	1	4	1	0	0	0
19	444	2	1	4	0	0	0
20	333	2	1	63	0	0	0
21	444	2	2	2	4	0	0
22	654	2	2	4	0	0	0
23	444	2	3	7	2	4	0
;;;;
   run;
proc sort;
   by userid cat day;
   run;
proc print;
   run;
data lags2;
   do until(last.cat);
      set lags;
      by userid cat;
      array t[4] _temporary_;
      t[day]=value;
      array x[3];
      if day gt 1 then x[1]=t[day-1];
      if day gt 2 then x[2]=t[day-2];
      if day gt 3 then x[3]=t[day-3];
      output;
      end;
   call missing(of t[*]);
   run;
proc print;
   run;

This would be easier if you expanded the data to fill in the missing days.

Values

PosVal(d-1)

PosVal(d-2)

JoMa — Sun, 14 Feb 2016 20:03:38 GMT

Thanks a lot!!!!!