BookmarkSubscribeRSS Feed
groverarena
Calcite | Level 5

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

11 REPLIES 11
allurai0412
Fluorite | Level 6

hi ,

use weekday like ...

if weekday(to_date) = 1 ....

Regards,

Allu

groverarena
Calcite | Level 5

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

allurai0412
Fluorite | Level 6

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

Reeza
Super User

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.

groverarena
Calcite | Level 5

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

groverarena
Calcite | Level 5

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?


Astounding
PROC Star

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?

groverarena
Calcite | Level 5

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.

Astounding
PROC Star

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.

Astounding
PROC Star

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.

allurai0412
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1309 views
  • 3 likes
  • 4 in conversation