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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 541 views
  • 0 likes
  • 3 in conversation