Help using Base SAS procedures

Splitting up accounts on a weekly basis

Reply
Occasional Contributor
Posts: 5

Splitting up accounts on a weekly basis

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

Frequent Contributor
Posts: 97

Re: Splitting up accounts on a weekly basis

hi ,

use weekday like ...

if weekday(to_date) = 1 ....

Regards,

Allu

Occasional Contributor
Posts: 5

Re: Splitting up accounts on a weekly basis

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

Frequent Contributor
Posts: 97

Re: Splitting up accounts on a weekly basis

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

Super User
Posts: 17,819

Re: Splitting up accounts on a weekly basis

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.

Occasional Contributor
Posts: 5

Re: Splitting up accounts on a weekly basis

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

Occasional Contributor
Posts: 5

Re: Splitting up accounts on a weekly basis

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?


Super User
Posts: 5,081

Re: Splitting up accounts on a weekly basis

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?

Occasional Contributor
Posts: 5

Re: Splitting up accounts on a weekly basis

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.

Super User
Posts: 5,081

Re: Splitting up accounts on a weekly basis

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.

Super User
Posts: 5,081

Re: Splitting up accounts on a weekly basis

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.

Frequent Contributor
Posts: 97

Re: Splitting up accounts on a weekly basis

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

Ask a Question
Discussion stats
  • 11 replies
  • 255 views
  • 3 likes
  • 4 in conversation