BookmarkSubscribeRSS Feed
kumarK
Quartz | Level 8

Hi All,

I have this dataset ordered by ID. I want cumulative sum with respective of grand total. if the cumulative sum crosses the grand total then not considering those records and flag those records as "Crosed Gtotal" or any other, then the cumulative sum goes to the the next records.

Here Gtotal is: 60ding some rows

data have;

input id Count;

cards;

101 15

102 35

103 30

104 25

105 50

106 4

107 25

108 5

run;

Data want:

BranchCountTotal
1011515
1023550
10330crosses GTotal
10425crosses GTotal
10550crosses GTotal
106454
10725crosses GTotal
108559
3 REPLIES 3
andreas_lds
Jade | Level 19

Not sure if i fully understood your requirement.

%let GrandTotal = 60;

data work.want;

  set work.have;

  length Total 8;

  retain Total 0;


  Total = Total + Count;

  output;

  if Total >= &GrandTotal then do;

     Total = Total - Count;

  end;

run;

proc format;

  value GTotal

    low -< &GrandTotal = [best32.]

    &GrandTotal - high = 'crosses GTotal'

  ;

run;

proc print data=work.want;

  format Total GTotal.;

run;

kumarK
Quartz | Level 8

Thanks Andreas,

This is what i am looking. Thanks for your time.

Can we find out the crossed GTotatal id's row numbers?  ie. for 103 -row num is 3 , for  104 row num is 4 .

andreas_lds
Jade | Level 19

Sorry for the delayed response, summer holiday started earlier than normal this year 😉

The automatic-variable "_n_" contains the observation-number - as long as you don't use a where statement in the data step.

data work.want;

  set work.have;

  length Total RowNumber 8;
  retain Total 0;

  Total = Total + Count;

  if Total >= &GrandTotal then do;
    RowNumber = _n_;
  end;

  output;

  if Total >= &GrandTotal then do;
     Total = Total - Count;    
  end;
run;


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!

How to Concatenate Values

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.

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
  • 3 replies
  • 1934 views
  • 3 likes
  • 2 in conversation