topic Re: generating new variable based on max cumulative sum in SAS Programming

generating new variable based on max cumulative sum

stevenyan0127 — Sat, 09 Apr 2022 00:49:52 GMT

Hi, I have a dataset with 3 varibales, ID, Date, Cost in a long format. I want to generate a new variable called "Date_over_100" that shows the date when one's cumulative cost have exceeded 100. I would appreciate all the help there is. Thanks in advance!

Re: generating new variable based on max cumulative sum

mkeintz — Sat, 09 Apr 2022 02:52:28 GMT

Help us help you.

Please provide a working DATA step with your sample data.

And please provide a well-defined table showing what you expect the output dataset to look like.

generating new variable based on cumulative sum

stevenyan0127 — Sat, 09 Apr 2022 04:13:47 GMT

1. I created the dataset first. I have repeated the generated 10 IDs so there are 100 in total, and generated 10 repetitions of 10 dates for each ID, and generated values for cost by random sampling 100 numbers between 30 and 50. And the output would look like the attachment file.

set.seed(11)
ID <- c(rep(1:10,each=10))
Date <- rep(sample(seq(as.Date('2008/01/01'), as.Date('2008/01/11'), by="day",replace=T), 10),10)
Cost <- round(runif(100,30,50),digits = 2)
df <- data.frame(ID,Date,Cost)
df <- df%>%
      arrange(Date)

2. I created a variable named "Date_Over_100" to show the date when one's cumulative cost has exceeded 100. Below are codes and output.

df_new <- df%>%
  group_by(ID) %>%
  summarize(Date_over_100 = Date[which.max(cumsum(Cost) > 100)])

3. I then transformed the original dataset into wide format.

df_wide <- df%>%
  arrange(Date)%>%
  pivot_wider(names_from = Date, values_from = Cost)

4. Lastly, I merged the wide format dataset with the dataset I got in 3.

df_final <- inner_join(df_new,df_wide,by="ID")

Now in SAS:

1. Creating the dataset.

data a;
   call streaminit(123);
	do ID = 1 to 10;
	   do Date = '01Jan2008'd to '10Jan2008'd;
              Cost = rand("integer",30,50);
	      output;
	   end;
	end;
   format Date yymmddd10.;
run;

First 15 rows of the output:

And next I need to create the variable "Date_over_100" that shows the date when one's cumulative cost has exceeded 100.

I have tried to replicate what I have done in R into SAS but haven't succeeded. I would appreciate any help there is. Thanks in advance!

Re: generating new variable based on max cumulative sum

stevenyan0127 — Sat, 09 Apr 2022 04:14:50 GMT

Hi, thanks for the reply. I have started a new thread with very detailed steps. https://communities.sas.com/t5/SAS-Programming/generating-new-variable-based-on-cumulative-sum/m-p/806906#M318001 Would you mind taking a look? Thank you very much!

Re: generating new variable based on cumulative sum

Kurt_Bremser — Sat, 09 Apr 2022 06:26:50 GMT

See this:

dara want;
set a;
by id;
retain flag;
if first.id
then do;
  sum_cost = cost;
  flag = 1;
end;
else sum_cost + cost; /* sum statement implies retain of sum_cost */
if sum_cost > 100
then do;
  if flag then output;
  flag = 0;
end;
drop flag;
run;

Untested, posted from my tablet.

PS I merged your questions, no need for a new thread.

Re: generating new variable based on max cumulative sum

mkeintz — Sat, 09 Apr 2022 10:59:14 GMT

For each ID, I think you want one observation, with variable ID and DATE, representing the first date in which the cumulative sum of costs exceeds 100.

If so, then:

data want;
  set a;
  by id;
  if first.id then sum_cost=0;
  sum_cost+cost;
  if (lag(sum_cost)<=100 or first.id=1) and sum_cost>100;
run;

The "subsetting if" outputs an observation only when the current sum_cost>100 and the observation-in-hand is either the first observation for a given ID, or is immediately preceded by an observation with sum_cost<=100.

Re: generating new variable based on cumulative sum

stevenyan0127 — Sat, 09 Apr 2022 21:42:28 GMT

Thank you Kurt! It works perfect!

Re: generating new variable based on max cumulative sum

stevenyan0127 — Sat, 09 Apr 2022 21:42:43 GMT

Thank you very much! It works perfect!