topic Re: Append a new dataset to a existing dataset conditionally in SAS Programming

Append a new dataset to a existing dataset conditionally

cczzzl — Wed, 13 Dec 2017 13:59:24 GMT

Hi,

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

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!

Re: Append a new dataset to a existing dataset conditionally

Astounding — Wed, 13 Dec 2017 14:56:13 GMT

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;

Re: Append a new dataset to a existing dataset conditionally

ballardw — Wed, 13 Dec 2017 15:58:00 GMT

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.

Re: Append a new dataset to a existing dataset conditionally

cczzzl — Thu, 14 Dec 2017 11:20:03 GMT

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!

Re: Append a new dataset to a existing dataset conditionally

cczzzl — Thu, 14 Dec 2017 11:22:26 GMT

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.