Hi,
I have the table below, customer_id and the subscription date. I want to add another field showing the Saturday date before the sub date.
Your help would be much appreciated.
Thank You very much
Have |
|||
customer_id |
sub_table_date |
||
50095 |
22-Dec-15 |
||
50206 |
10-Jan-16 |
||
50220 |
26-May-16 |
||
Want |
|||
customer_id |
sub_table_date |
Saturday_before_dt |
|
50095 |
22-Dec-15 |
19-Dec-15 |
|
50206 |
10-Jan-16 |
04-Jun-16 |
|
50220 |
26-May-16 |
21-May-16 |
!!Post test data in the form of a datastep!!
Its not hard, and it saves lots of questions!!!
As such I am guessing dates are dates:
data have; customer_id=50095; sub_table_date="22Dec2015"d; run; data want; set have; sat_date=sub_table_date; do while (weekday(sat_date) ne 7); sat_date=sat_date-1; end; format sub_table_date sat_date date9.; run;
data have;
input customer_id sub_table_date :date9.;
datalines;
50095 22Dec2015
50206 10Jan2016
50220 26May2016
;
data want;
set have;
do _n_=sub_table_date by -1 until(weekday(_n_)=7);
if weekday(_n_)=7 then Saturday_before_dt=_n_;
end;
format sub_table_date Saturday_before_dt date9.;
run;
Regards,
Naveen Srinivasan
!!Post test data in the form of a datastep!!
Its not hard, and it saves lots of questions!!!
As such I am guessing dates are dates:
data have; customer_id=50095; sub_table_date="22Dec2015"d; run; data want; set have; sat_date=sub_table_date; do while (weekday(sat_date) ne 7); sat_date=sat_date-1; end; format sub_table_date sat_date date9.; run;
Like this?
data Have;
input customer_id$ sub_table_date:date7.;
format sub_table_date date7.;
datalines;
50095 22-Dec-15
50206 10-Jan-16
50220 26-May-16
;
data want;
set have;
Saturday_before_dt = intnx('week',sub_table_date,-1, 'end');
format Saturday_before_dt date7.;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.