Splitting up accounts on a weekly basis
Problem Description:
Hi,
I have a daily balance account sheet which has 5columns. account number product_type balance from_dt to_dt
12345 4001 25$ 01/25/2009 02/24/2009
12345 4001 25.25$ 02/25/2009 03/24/2009
It captures the record only when the balance is changed. Can be a day, week,month etc Right now this is a daily balance report. I
need a report as of every sunday i.e after every 7 days and reflect what the balance was.
What code should i write so it can split up the balances by account number on a weekly basis starting from first from_dt i.e 01/25/2009.
Thanks
Regards
CG
hi ,
use weekday like ...
if weekday(to_date) = 1 ....
Regards,
Allu
Hi Allu,
I am not able to understand. Can you please provide with some example code?
account number product_type balance from_dt to_dt
12345 4001 25$ 01/25/2009 02/24/2009
12345 4001 25.25$ 02/25/2009 03/24/2009
hi ,
try this....your SUNDAY will be in ds3.....other than SUNDAYS in ds4
data have;
input product type balance dollar6.0 @19 from_dt mmddyy10.@31 to_dt mmddyy10.;
format from_dt mmddyy10. to_dt mmddyy10.;
datalines;
12345 4001 $25 01/25/2009 02/24/2009
12345 4001 $25.25 02/25/2009 03/24/2009
;
run;
data sample ds3 (Drop=y) ds4;
set have;
format i mmddyy10.;
do i = '25jan2009'd to '24mar2009'd; /*Here you can change latest or up to date)*/
y=weekday(i);
if y=1 then output ds3;
else
output ds4;
end;
i+1;
run;
Regards,
Allu
What do you want your output to look like? Is it a single text file with dates, or reports to HMTL for each week, that are run each week?
The premise would be to take your starting date 01/25/2009 and then increment that weekly, via the intnx function.
Then you would find all the records where the from-to date intersect your date of interest and use that for the report.
I have a SAS dataset name daily balance. I want to create a new one with weekly balance. from_dt and to_dt represent the range when balances changed. It is a daily file but to_dt changes only when there is a change in balance. I want to create asofdate for each account which showw weekly balance rather than change date of balance.
For example :
account number product_type balance from_dt to_dt
12345 4001 25$ 01/25/2009 02/24/2009
12345 4001 25.25$ 02/25/2009 03/24/2009
23456 4002 26 01/26/2009 01/26/2009
23456 4002 26.27 01/27/2009 01/29/2009
Message was edited by: Anuj Grover
so the output dataset should look something like
12345 4001 25$ 01/25/2009 --showing weekly split of balances
12345 4001 25$ 02/01/2009
12345 4001 25$ 02/08/2009
12345 4001 25$ 02/14/2009
12345 4001 25$ 02/21/2009
12345 4001 25.25$ 02/28/2009 (because balance changed on 02/25/2009) as end of this week balance is 25.25$
Does that makes sense?
Here's what I would recommend as a first step. Construct a SAS data set with two variables: ACCOUNT_NUMBER and SUNDAY_DATE. There will be one observation for each Sunday that is relevant to that account number. After that, the programming is tricky but short. Is that something you can do, or would you need help with that first step?
Hi
I am pretty new to the SAS programming. If you can help out with the code i would really appreciate it.
Thanks for your help.
I'm not sure if I can devote enough time this afternoon, but let's set some guidelines first. Please confirm ...
The range of Sundays can vary by account.
For each account, the relevant Sundays are any that are >= the smallest FROM_DT, and <= the largest TO_DT applicable to that account.
All dates are stored as integers on SAS's date scale.
Other variables may exist on the data set, but do not have to be part of the final result.
OK, there is actually an easy way to do this. Assuming you have your dates properly set on the SAS date scale, try:
data want;
set have;
do sunday_date = from_dt to to_dt;
if weekday(sunday_date)=1 then output;
end;
run;
Good luck.
hi try this,
.........sample , ds4 are dataset nothing to do......other than these i guess code works....
data have;
input product type balance dollar6.0 @19 from_dt mmddyy10.@31 to_dt mmddyy10.;
format from_dt mmddyy10. to_dt mmddyy10.;
datalines;
12345 4001 $25 01/25/2009 02/24/2009
12345 4001 $25.25 02/25/2009 03/24/2009
23456 4002 $26 01/26/2009 01/26/2009
23456 4002 $26.27 01/27/2009 01/29/2009
;
run;
data sample ds3 (Drop=y KEEP=i) ds4;
set have;
format i mmddyy10.;
do i = '25jan2009'd to '24mar2009'd; /*Here you can change latest or up to date)*/
y=weekday(i);
if y=1 then output ds3;
else
output ds4;
end;
i+1;
run;
proc sql ;
SELECT distinct i,balance FROM HAVE a, ds3 b
where a.from_dt = (select max(from_dt) from have a1 where a1.from_dt<=b.i) group by b.i;
QUIT;
Regards,
Allu
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.