Hi,
I have this dataset
DATE CF N I
30-Oct-18 A1 1 1
30-Oct-18 A2 1 1
30-Oct-18 A3 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3
31-Dec-18 A4
and because CF A3 has a value of 2 in column I at date 30/10/2018, I want to update column I with 2 for CF=A3 for all subsequent months.
Could you please help me?
Thanks.
PLEASE start posting example data in usable form:
data have;
infile cards dsd dlm=' ' truncover;
input date :date9. cf $ n i;
format date yymmddd10.;
cards;
30-Oct-18 A1 1 1
30-Oct-18 A2 1 1
30-Oct-18 A3 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3
31-Dec-18 A4
;
run;
It's not rocket science and makes work much easier for your potential helpers. Consider it a basic courtesy.
From this data, use sorting and a data step with a retained variable:
proc sort data=have;
by cf date;
run;
data want;
set have;
by cf;
retain _i;
if first.cf then _i = .;
i = ifn(_i,_i,i);
if i = 2 then _i = 2;
drop _i;
run;
proc sort data=want;
by date cf;
run;
proc print data=want noobs;
run;
Result:
date cf n i 2018-10-30 A1 1 1 2018-10-30 A2 1 1 2018-10-30 A3 . 2 2018-11-30 A1 1 1 2018-11-30 A2 1 1 2018-11-30 A3 . 2 2018-12-31 A1 1 1 2018-12-31 A2 1 1 2018-12-31 A3 . 2 2018-12-31 A4 . .
data have;
input (DATE CF N I_ )(:$10.) ;
cards;
30-Oct-18 A1 1 1
30-Oct-18 A2 1 1
30-Oct-18 A3 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . .
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . .
31-Dec-18 A4 . .
;
data want;
if _n_=1 then do;
declare hash H (dataset: "have(where=(cf='A3' and not missing(i_))") ;
h.definekey ("cf") ;
h.definedata ("i_") ;
h.definedone () ;
end;
set have;
_iorc_=h.find();
run;
proc sql;
create table want as
select DATE,a.CF ,N,coalesce(a.i_,b.i_) as i_
from have a left join have(keep=cf i_ where=(cf='A3' and not missing(i_))) b
on a.cf=b.cf
order by date;
quit;
can you post the log?
Also ,
isn't your original stated requirement was/is to update for all CF='A3'?
Plus can you post a sample of your expected output like your input sample
29 order by date;
ERROR: The variable cf in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: The variable i_ in the DROP, KEEP, or RENAME list has never been referenced.
A more expanded dataset
DATE CF N IP
31-Dec-16 A1 1 1
31-Dec-16 A2 1 1
31-Dec-16 A3 . 2
31-Dec-16 A4 . .
31-Dec-16 A5 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . .
30-Nov-18 A4 . .
30-Nov-18 A5 . .
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . .
31-Dec-18 A4 . .
31-Dec-18 A5 . .
and the desired outcome
DATE CF N IP
31-Dec-16 A1 1 1
31-Dec-16 A2 1 1
31-Dec-16 A3 . 2
31-Dec-16 A4 . .
31-Dec-16 A5 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . 2
30-Nov-18 A4 . .
30-Nov-18 A5 . 2
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . 2
31-Dec-18 A4 . .
31-Dec-18 A5 . 2
Thank you @cmemtsa
data have;
input DATE :date9. CF $ N IP ;
format date date9.;
cards;
31-Dec-16 A1 1 1
31-Dec-16 A2 1 1
31-Dec-16 A3 . 2
31-Dec-16 A4 . .
31-Dec-16 A5 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . .
30-Nov-18 A4 . .
30-Nov-18 A5 . .
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . .
31-Dec-18 A4 . .
31-Dec-18 A5 . .
;
data want;
if _n_=1 then do;
declare hash H (dataset: "have(where=(ip=2))") ;
h.definekey ("cf") ;
h.definedata ("ip") ;
h.definedone () ;
end;
set have;
_iorc_=h.find();
run;
And a similar change in proc sql
proc sql;
create table want as
select DATE,a.CF ,N,coalesce(b.ip,a.ip) as ip
from have a left join have(keep=cf ip where=(ip=2)) b
on a.cf=b.cf
order by date,cf;
quit;
Run my code, it gives you the desired result.
@cmemtsa wrote:
29 order by date;
ERROR: The variable cf in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: The variable i_ in the DROP, KEEP, or RENAME list has never been referenced.
A more expanded dataset
DATE CF N IP
31-Dec-16 A1 1 1
31-Dec-16 A2 1 1
31-Dec-16 A3 . 2
31-Dec-16 A4 . .
31-Dec-16 A5 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . .
30-Nov-18 A4 . .
30-Nov-18 A5 . .
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . .
31-Dec-18 A4 . .
31-Dec-18 A5 . .
and the desired outcome
DATE CF N IP
31-Dec-16 A1 1 1
31-Dec-16 A2 1 1
31-Dec-16 A3 . 2
31-Dec-16 A4 . .
31-Dec-16 A5 . 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3 . 2
30-Nov-18 A4 . .
30-Nov-18 A5 . 2
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3 . 2
31-Dec-18 A4 . .
31-Dec-18 A5 . 2
PLEASE start posting example data in usable form:
data have;
infile cards dsd dlm=' ' truncover;
input date :date9. cf $ n i;
format date yymmddd10.;
cards;
30-Oct-18 A1 1 1
30-Oct-18 A2 1 1
30-Oct-18 A3 2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3
31-Dec-18 A4
;
run;
It's not rocket science and makes work much easier for your potential helpers. Consider it a basic courtesy.
From this data, use sorting and a data step with a retained variable:
proc sort data=have;
by cf date;
run;
data want;
set have;
by cf;
retain _i;
if first.cf then _i = .;
i = ifn(_i,_i,i);
if i = 2 then _i = 2;
drop _i;
run;
proc sort data=want;
by date cf;
run;
proc print data=want noobs;
run;
Result:
date cf n i 2018-10-30 A1 1 1 2018-10-30 A2 1 1 2018-10-30 A3 . 2 2018-11-30 A1 1 1 2018-11-30 A2 1 1 2018-11-30 A3 . 2 2018-12-31 A1 1 1 2018-12-31 A2 1 1 2018-12-31 A3 . 2 2018-12-31 A4 . .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.