Capture value from row in table base column values in EG

[gregor1]

I have the following table in SAS EG:

Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago

11/19/2020   BUSINESS RULES           21    11/20/2019                      0
11/19/2020   OTHER DEPT RFRRLS     5    11/20/2019                       0
11/18/2020   BUSINESS RULES       1267    11/19/2019                      0
11/18/2020   PROPERTY SURVEY     144    11/19/2019                      0
11/18/2020   OTHER DEPT RFRRLS    69    11/19/2019                      0

The "365 Days Ago" column is calculated by this formula:

INTNX('day',t1.REFERRED_DATE, -365)

Volume that corresponds to the "365 Days Ago" date and the Referral Type is further down in the table in the "Vol" column.  I am trying to get the value from the "Vol" column to populate the "Vol 365 Days Ago" column, where the values in "Date" column and the "Referral Type" align with the "365 Days Ago" value.  Fore example, if the volume for the date of 11/20/2019 where the type is BUSINESS RULES is 50, then the first row returned would be:

Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago

11/19/2020   BUSINESS RULES           21    11/20/2019                      50

I have been search the Community library but not finding what I need.  Any thoughts/ideas?

Thanks!

[Reeza · Accepted solution]

It's a straightforward merge, but do you want 365 days ago or one year ago?
Those are technically different as you can see in your example above.

proc sql;
create table want as
select t1.*, t2.vol as vol_365daysago
from have as t1
left join have t2
on t1.date_365daysago = t2.date
and t1.referral_type = t2.referral_type;
quit;

I suspect that will need not work for your actual problem as the join needs to join on other factors than just date and referral type, likely an ID of some kind but should get you started.
https://www.listendata.com/2014/06/proc-sql-merging.html

[gregor1]

Thanks Reeza for your reply! I kept working on it after I posted my problem and came up with a similar approach as yours and it worked. I truly appreciate your help and response. Thanks!

[ballardw]

---

@gregor1 wrote:

I have the following table in SAS EG:

Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago

11/19/2020   BUSINESS RULES           21    11/20/2019                      0
11/19/2020   OTHER DEPT RFRRLS     5    11/20/2019                       0
11/18/2020   BUSINESS RULES       1267    11/19/2019                      0
11/18/2020   PROPERTY SURVEY     144    11/19/2019                      0
11/18/2020   OTHER DEPT RFRRLS    69    11/19/2019                      0

The "365 Days Ago" column is calculated by this formula:

INTNX('day',t1.REFERRED_DATE, -365)

Volume that corresponds to the "365 Days Ago" date and the Referral Type is further down in the table in the "Vol" column.  I am trying to get the value from the "Vol" column to populate the "Vol 365 Days Ago" column, where the values in "Date" column and the "Referral Type" align with the "365 Days Ago" value.  Fore example, if the volume for the date of 11/20/2019 where the type is BUSINESS RULES is 50, then the first row returned would be:

Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago

11/19/2020   BUSINESS RULES           21    11/20/2019                      50

I have been search the Community library but not finding what I need.  Any thoughts/ideas?

Thanks!

---

One way to do such is to use Proc SQL with a reflexive join (the table with itself).

Since you did not provide any values that "match" can't provide code for that.

Here is an example of a reflexive join on a single value:

data have;
   do time = 1 to 50;
      value = rand('integer',10);
      output;
   end;
run;

proc sql;
   create table want as
   select a.time,a.value
          ,b.time as otime, b.value as ovalue
   from have as a
        left join
        have as b
        on (a.time - 12)=b.time
   ;
quit;

You could use your intnx expression for one of the sides, such as the (a.time -12).

Add other requirements that must match to the ON by using "and a.variablename = b.variablename" for simple matches.

The join done this way may find more than one match depending on your data and each match will be in the output.

Note that actually creating the additional date variable is not needed. If you remove the "b.time as otime" you still get the ovalue that corresponds.

Use of a Left join this way will have all of the records in the have set appear at least once. If you want records in the output that only have the corresponding Ovalue you could add a : Where not missing(b.value) after the ON.

[gregor1]

Thanks Ballardw! I figured out using this method after I presented my problem but before you replied. I truly appreciate you help and response.

[mkeintz]

Are your data sorted chronologically?  If so, then you can maintain a volume history in an array, such that when you encounter a give date and and referral type, you can retrieve the volume for that referral type from 365 days prior:

%let hist_beg=%sysevalf("01jan2018"d);
%let hist_end=%sysevalf("31dec2020"d);

data want (drop=_:);
  set have;

  array vol_hist {3,&hist_beg:&hist_end} _temporary_;
  date_365_days_ago=intnx('day',date,-365);
  select(referral_type);
    when ("BUSINESS RULES")    _row=1;
    when ("OTHER DEPT RFRRLS") _row=2;
    when ("PROPERTY SURVEY")   _row=3;
  end;
  vol_365_days_ago=vol_hist{_row,date_365_days_ago};
  vol_hist{_row,date}=vol;
run;

The temporary array VOL_HIST in this example has 3 rows, one for each referral type.  And it has one column for your historical period (I set it to 01jan2019 through 31dec2020, but you can easily change it.  But of indexing the column numbers as 1,2, 3, ..., it is indexed on the underlying date values 01jan2018, 02jan2018, ... 31dec2020.

VOL_HIST has hundreds of data elements, but because it is a temporary array, there won't be hundreds of variables added to your output data set.  And it's values will not be reset to missing with each incoming observation - that's why it can hold a historic series of values.

[gregor1]

Thanks Mkeintz! Great explanation and I will give it a try.