Append a new dataset to a existing dataset conditionally

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Append a new dataset to a existing dataset conditionally

[ Edited ]

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!


Accepted Solutions
Solution
‎12-20-2017 12:20 AM
Super User
Posts: 5,995

Re: Append a new dataset to a existing dataset conditionally

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


All Replies
Solution
‎12-20-2017 12:20 AM
Super User
Posts: 5,995

Re: Append a new dataset to a existing dataset conditionally

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;

Occasional Contributor
Posts: 10

Re: Append a new dataset to a existing dataset conditionally

Posted in reply to Astounding
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.
Super User
Posts: 12,148

Re: Append a new dataset to a existing dataset conditionally

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.

Occasional Contributor
Posts: 10

Re: Append a new dataset to a existing dataset conditionally

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!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 101 views
  • 0 likes
  • 3 in conversation