BookmarkSubscribeRSS Feed
hk2013
Fluorite | Level 6

 

for current month i want the output to equal num_0 but for future months i want it to equal to past months Num_1.

data have; input id num_0 date:date9. num_1 ; format date date9.; datalines; 1 20 06JAN2019 50 1 20 13JAN2019 50 1 20 20JAN2019 50 1 20 27JAN2019 50 1 20 03FEB2019 100 1 20 10FEB2019 100 1 20 17FEB2019 100 1 20 24FEB2019 100 1 20 03MAR2019 150
1 20 10MAR2019 150
1 20 17MAR2019 150
1 20 24MAR2019 150
; run;

output i want : 

id num_0 date num_1 num_2
1 20 06JAN2019 50 20
1 20 13JAN2019 50 20
1 20 20JAN2019 50 20
1 20 27JAN2019 50 20
1 20 03FEB2019 100 50
1 20 10FEB2019 100 50
1 20 17FEB2019 100 50
1 20 24FEB2019 100 50
1 20 03MAR2019 150 100
1 20 10MAR2019 150 100
1 20 17MAR2019 150 100
1 20 24MAR2019 150 100

2 REPLIES 2
novinosrin
Tourmaline | Level 20

data have;
input id num_0  date :date9. num_1 ;
format date date9.;
datalines; 
1 20 06JAN2019 50  
1 20 13JAN2019 50
1  20 20JAN2019 50 
1  20 27JAN2019 50
1 20 03FEB2019  100
1 20 10FEB2019 100
1  20 17FEB2019  100
1 20  24FEB2019  100
1 20 03MAR2019  150
1 20 10MAR2019 150
1 20 17MAR2019 150
1 20 24MAR2019 150
;
data temp;
set have;
by id date ;
t=put(date,monyy.);/*assigning month year for by group creation*/
run;
data want;
set temp;
by id  t notsorted;
retain k num_2;
if _n_=1 then k=put(today(),monyy.) ;/*getting the current month*/
if first.t then  num_2=lag(num_1);
if k=t then num_2=num_0;
drop t k;
run;
Ksharp
Super User

data have;
input id num_0  date :date9. num_1 ;
format date date9.;
datalines; 
1 20 06JAN2019 50  
1 20 13JAN2019 50
1  20 20JAN2019 50 
1  20 27JAN2019 50
1 20 03FEB2019  100
1 20 10FEB2019 100
1  20 17FEB2019  100
1 20  24FEB2019  100
1 20 03MAR2019  150
1 20 10MAR2019 150
1 20 17MAR2019 150
1 20 24MAR2019 150
;
data have;
set have;
_date=intnx('month',date,0);
format _date date9.;
run;
data temp;
set have;
_date=intnx('month',date,1);
format _date date9.;
run;
data want;
merge have(in=ina) temp(keep=id _date num_1 rename=(num_1=_num_1));
by id _date;
if year(today())=year(date) and month(today())=month(date) then  num_2= num_0;
 else  num_2= _num_1;
if ina;
drop _: ;
run;