topic Re: Splitting up accounts on a weekly basis in SAS Procedures

Splitting up accounts on a weekly basis

groverarena — Fri, 31 May 2013 15:09:52 GMT

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

Re: Splitting up accounts on a weekly basis

allurai0412 — Fri, 31 May 2013 16:46:06 GMT

hi ,

use weekday like ...

if weekday(to_date) = 1 ....

Regards,

Allu

Re: Splitting up accounts on a weekly basis

groverarena — Fri, 31 May 2013 16:50:28 GMT

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

Re: Splitting up accounts on a weekly basis

Reeza — Fri, 31 May 2013 17:18:12 GMT

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.

Re: Splitting up accounts on a weekly basis

allurai0412 — Fri, 31 May 2013 17:36:04 GMT

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

Re: Splitting up accounts on a weekly basis

groverarena — Fri, 31 May 2013 17:42:39 GMT

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

Re: Splitting up accounts on a weekly basis

groverarena — Fri, 31 May 2013 17:51:06 GMT

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?

Re: Splitting up accounts on a weekly basis

Astounding — Fri, 31 May 2013 18:15:29 GMT

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?

Re: Splitting up accounts on a weekly basis

groverarena — Fri, 31 May 2013 18:19:01 GMT

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.

Re: Splitting up accounts on a weekly basis

Astounding — Fri, 31 May 2013 20:27:58 GMT

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.

Re: Splitting up accounts on a weekly basis

Astounding — Fri, 31 May 2013 21:37:34 GMT

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.

Re: Splitting up accounts on a weekly basis

allurai0412 — Sat, 01 Jun 2013 12:55:10 GMT

hi try this,

.........sample , ds4 are dataset nothing to do......other than these i guess code works....

;
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