topic Re: How to sum the specific values with previous observation values when numbers less than three? in SAS Procedures

How to sum the specific values with previous observation values when numbers less than three?

elvics — Tue, 03 Sep 2019 11:03:04 GMT

Dear all

I want to check the numbers for each date.

The values need to be coded as missing when the numbers less than 3.

Then, the values needs to sum up with the previous values that shown as "New_Numbers".

(ex: 745=738+2+1+2+1+1)

The raw data are as follows:

Date	Numbers	New_Numbers
20100101	233	233
20100102	271	271
20100103	350	350
20100104	738	745
20100105	2
20100106	1
20100107	2
20100108	1
20100109	1
20100110	413	413
20100111	710	710
20100113	575	575
20100114	543	545
20100115	2
20100116	296	296
20100117	369	369
20100118	735	735
20100119	616	616
20100120	571	571
20100122	458	460
20100123	1
20100124	1
20100125	664	664
20100126	575	575
20100127	574	574
20100129	415	415
20100130	269	269
20100131	360	362
20100201	2

Re: How to sum the specific values with previous observation values when numbers less than three?

novinosrin — Tue, 03 Sep 2019 11:39:36 GMT

Hello @elvics


data have;
infile cards truncover;
input Date :yymmdd10.	Numbers	;
format date yymmdd10.;
cards;
20100101	233	233
20100102	271	271
20100103	350	350
20100104	738	745
20100105	2	　
20100106	1	　
20100107	2	　
20100108	1	　
20100109	1	　
20100110	413	413
20100111	710	710
20100113	575	575
20100114	543	545
20100115	2	 
20100116	296	296
20100117	369	369
20100118	735	735
20100119	616	616
20100120	571	571
20100122	458	460
20100123	1	 
20100124	1	 
20100125	664	664
20100126	575	575
20100127	574	574
20100129	415	415
20100130	269	269
20100131	360	362
20100201	2	 
;

data want ;
if _n_=1 then do;
 if 0 then set have(rename=(numbers=n));
   dcl hash H (dataset:'have(rename=(numbers=n))') ;
   h.definekey  ("date") ;
   h.definedata ("n") ;
   h.definedone () ;
end;
set have;
if numbers>=3 then do;
do d=date+1 by 1 while(h.find(key:d)= 0);
 if n>=3  then leave;
 new_numbers=sum(new_numbers,n);
end;
new_numbers=sum(new_numbers,numbers);
end;
drop n d;
run;

Re: How to sum the specific values with previous observation values when numbers less than three?

Kurt_Bremser — Tue, 03 Sep 2019 11:43:00 GMT

Sort in reverse order.

Then, in a data step, retain a sum variable.

Set to zero at the beginning.

When numbers is le 3, sum the values up in your retained variable;

When numbers is gt 3, then:

add retained variable to numbers to give new_numbers
output
set retained variable to zero

Sort back to original order.

For code (especially tested code) supply example data in usable form (data step with datalines for easy copy/paste to program window)

Re: How to sum the specific values with previous observation values when numbers less than three?

novinosrin — Tue, 03 Sep 2019 12:00:55 GMT

And a traditional method



data have;
infile cards truncover;
input Date :yymmdd10.	Numbers	;
format date yymmdd10.;
cards;
20100101	233	233
20100102	271	271
20100103	350	350
20100104	738	745
20100105	2	　
20100106	1	　
20100107	2	　
20100108	1	　
20100109	1	　
20100110	413	413
20100111	710	710
20100113	575	575
20100114	543	545
20100115	2	 
20100116	296	296
20100117	369	369
20100118	735	735
20100119	616	616
20100120	571	571
20100122	458	460
20100123	1	 
20100124	1	 
20100125	664	664
20100126	575	575
20100127	574	574
20100129	415	415
20100130	269	269
20100131	360	362
20100201	2	 
;

data temp;
set have;
if numbers>=3 then grp+1;
run;

proc sql;
create table want(drop=grp) as
select *, ifn(numbers>=3,sum(numbers),.) as new_numbers
from temp
group by grp
order by date;
quit;

Re: How to sum the specific values with previous observation values when numbers less than three?

Jagadishkatam — Tue, 03 Sep 2019 12:42:32 GMT

Alternatively, please try

proc sort data=have ;
by descending date;
run;

data want;
set have;
by descending date;
if .<Numbers<=3 then grp=1;
else grp=2;
run;

data want2;
set want;
by grp notsorted;
if first.grp then sum=.;
if grp=1 then sum+numbers;
numbers2=coalesce(numbers+lag(sum),numbers);
if grp=1 then numbers2=.;
drop grp sum;
run;

proc sort data=want2 ;
by date;
run;