BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cczzzl
Calcite | Level 5

Hi,

 

I have a dataset with a single obs, which is like:

微信截图_20171213213954.png

The num here is computed cumulatively based on another column ( sum of the value in that column)

The condition defined as whether the num is smaller than a specific value.

If yes, then I need to append a new dataset to this dataset and compute the cumulative value (including the "num" here) again until it meets the condition, then I can extract this obs into a dataset with a single value.

If not, I just want to keep the dataset.

 

The code I use:

data want;

 set have;

if value <= 10000000 then do;

set new.have;

by name;

if first.obs then do;

value = value;end;

value + value1;

else if value > 10000000 then output;

run;

 

I am quite new to SAS, so any helps are much appreciated! 

Thank you very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Let's say you have a second data set named MORE_VALUES, that contains a variable named VALUE1.  You could approach the problem in this way:

 

data want;

set have;

if value <= 10000000 then do until (value > 10000000);

   set more_values;

   value + value1;

end;

output;

stop;

keep name value;

run;

View solution in original post

4 REPLIES 4
Astounding
PROC Star

Let's say you have a second data set named MORE_VALUES, that contains a variable named VALUE1.  You could approach the problem in this way:

 

data want;

set have;

if value <= 10000000 then do until (value > 10000000);

   set more_values;

   value + value1;

end;

output;

stop;

keep name value;

run;

cczzzl
Calcite | Level 5
The thing the new data set is daily data, which means I have a lot of daily data not just one to append the existing data. So I need repeat the procedure until the condition is satisfied.
ballardw
Super User

I'm going to suggest a slightly different approach that isn't going to involve creating what I suspect would end up being a maintenance nightmare if you have to do this repetitively:

 

Combine multiple data sets

Calculate a rolling cumulative for all of the data.

Select the data where the value of the cumulative value is less than or equal to your threshold value.

/* create two datasets just to have something to demostrate*/
data work.one;
   do x= 1 to 20;
     output;
   end;
run;

data work.two;
   do x= 5 to 25;
      output;
   end;
run;

data work.both;
   set work.one
       work.two
   ;
   retain cumx;
   cumx = sum(cumx,x);
   /* if the threshold value of the cumulative variable is 305*/
   output;
   if cumx ge 305 then stop;
run;

 

You may have to work on additional logic if you want the data where the cumulative value is only less than the threshold but doesn't exceed it.

cczzzl
Calcite | Level 5
I thought about it as well.
But the daily data are quite large ( up to 4G per dataset), so it may be slower using this logic.
Anyway, thank you very much!

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
  • 4 replies
  • 401 views
  • 0 likes
  • 3 in conversation