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!
@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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.