topic Re: Retrieve value from observation of previous period in SAS Programming

Retrieve value from observation of previous period

as_methodology — Fri, 13 Mar 2020 09:51:14 GMT

Hello everyone,

A couple of weeks ago I asked a question on how to identify whether an observation was present in different period. @PeterClemmensen 's answer was very useful and I have been successfully been working with it:

https://communities.sas.com/t5/SAS-Programming/Identify-whether-observation-was-present-in-different-period/m-p/621088#

Now, the issue arose that I do not only need to know whether the observation was present in the previous period, but also to retrieve a value from that observation.

The data and the solution from my previous post looks somwhat like this:

data have;
input year id value;
datalines;
2000 1 123
2000 2 456
2000 3 789
2000 4 012
2000 5 345
2001 1 678
2001 2 901
2001 3 234
2001 6 567
2001 7 890
2001 8 987
2002 1 654
2002 2 321
2002 6 098
2002 7 765
2002 9 432
2002 10 109
;
run;


data want;
if _N_=1 then do;
declare hash h (dataset : "have");
h.definekey ("year", "id");
h.definedone();
end;

set have;

present = ifn(h.check (key : year-1, key : id) = 0, 1, 0);
run;

Now I want to retrieve the value form "value". So for example in year 2002, it shoud return the value "678" for ID "1", "901" for "2" and so on.

I am aware that I have to add

h.definedata ("value");

to the statement. But I cannot find out, how to call the value afterwards.

Thank you very much already for your help!

Re: Retrieve value from observation of previous period

PeterClemmensen — Fri, 13 Mar 2020 10:15:21 GMT

Where do you want the 'lookup' value to go? Replacing the original value? If so, then simply do

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have");
        h.definekey    ("year", "id");
        h.definedata   ("value");
        h.definedone   ();
    end;

    set have;

    present = h.find (key : year-1, key : id) = 0;
run;

Re: Retrieve value from observation of previous period

ed_sas_member — Fri, 13 Mar 2020 10:16:38 GMT

Hi @as_methodology

Here is a way to achieve this using PROC SQL:

proc sql;
	select a.*,
		   case when b.value > 0 then 1 else 0 end as present,
		   b.value as prev_value
	from have as a left join have as b
	on a.id = b.id and a.year = (b.year+1)
	order by year, id;
quit;

Hope this helps!

Best,

Re: Retrieve value from observation of previous period

as_methodology — Fri, 13 Mar 2020 10:17:36 GMT

Hi @PeterClemmensen

Thanks for your answer. It should create a new variable, for example in the column "present". So instead for "1" it should return the value from the previous period.

Re: Retrieve value from observation of previous period

PeterClemmensen — Fri, 13 Mar 2020 10:25:00 GMT

Ok. No problem

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have(rename=value=lagvalue");
        h.definekey    ("year", "id");
        h.definedata   ("lagvalue");
        h.definedone   ();
    end;

    set have;
    lagvalue = .;

    present = h.find (key : year-1, key : id) = 0;
run;

Re: Retrieve value from observation of previous period

as_methodology — Fri, 13 Mar 2020 11:29:50 GMT

Thank you very much for your answer. This works fine, if one has to check only the previous period (which I stated in my question). However, my actual problem is a little bit more complicated and I have to check different time periods (t-1, t-2, t-3, but also t+1 and t+2). I didn't mention this before, since I wanted to state the question as easily as possible. And in this situation, I cannot figure out how to generalize your solution in such a situation.

Re: Retrieve value from observation of previous period

PeterClemmensen — Fri, 13 Mar 2020 11:48:35 GMT

Here is a general solution, that looks up different time periods and assigns the values to different variables.

Feel free to ask 🙂

data want (drop=rc v vt t);
    if _N_=1 then do;
        declare hash h (dataset : "have(rename=value=v");
        h.definekey    ("year", "id");
        h.definedata   ("v");
        h.definedone   ();
    end;

    set have;
    v = .;
    array vv {-3 : 2} vtminus3 vtminus2 vtminus1 vt vtplus1 vtplus2;

    do t = -3 to 2;
        rc = h.find (key : year + t, key : id);
        vv [t] = v;
    end;
run;

Re: Retrieve value from observation of previous period

novinosrin — Fri, 13 Mar 2020 12:16:34 GMT

Hi @as_methodology Good morning, Can you please post the expected output/input sample that you mentioned seems complicated. For some reason, I am unable to comprehend the thread well. You may write a brief description along with the samples. Thank you!

Re: Retrieve value from observation of previous period

as_methodology — Thu, 26 Mar 2020 07:13:25 GMT

So, after the office changed into home-office mode, I finally get to thank @PeterClemmensen for your help! This has made my life so much simpler! And I already included it in my process.

As a quick feedback to you: When including it, I noticed that the code looks for a value in the future, say t+2, and it cannot find one because it does not exist, it will return the last value it was able to find, in this case t+1. Surprinsingly, this works fine for values in the past.

I used the solution you gave to my previous post to work around it, and it works perfectly. So, thank you very much again!

Re: Retrieve value from observation of previous period

as_methodology — Thu, 26 Mar 2020 07:57:18 GMT

@ed_sas_member Thank you very much! This also works very nice. And I was even able to generalize it...

PROC SQL;
   CREATE TABLE WORK.WANT AS 
   SELECT t1.year, 
          t1.id, 
          t1.value, 
          t2.id AS idTM1, 
          t2.value AS valueTM1, 
          t3.id AS idTM2, 
          t3.value AS valueTM2,
		  t4.id AS idTP1, 
          t4.value AS valueTP1,
		  t5.id AS idTP2, 
          t5.value AS valueTP2
      FROM WORK.HAVE t1
           LEFT JOIN WORK.HAVE t2 ON (t1.year-1 = t2.year) AND (t1.id = t2.id)
           LEFT JOIN WORK.HAVE t3 ON (t1.year-2 = t3.year) AND (t1.id = t3.id)
		   LEFT JOIN WORK.HAVE t4 ON (t1.year+1 = t4.year) AND (t1.id = t4.id)
		   LEFT JOIN WORK.HAVE t5 ON (t1.year+2 = t5.year) AND (t1.id = t5.id)
      ORDER BY t1.year,
               t1.id;
QUIT;

Re: Retrieve value from observation of previous period

as_methodology — Thu, 26 Mar 2020 08:01:19 GMT

hi @novinosrin Thank you very much for your answer. The basic question was to find out what the value of the observation was in a different time period (e.g. turnover of an enterprise in the previous period) and get that information, which is currently stored in a different line, in the same line. As the answers from the others show, it should look like this

Re: Retrieve value from observation of previous period

PeterClemmensen — Thu, 26 Mar 2020 08:36:03 GMT

Thank you for your feedback 🙂 And glad the code work for you. Please remember to close the thread. This helps future users navigate the community.