ID Date Parameter
1 1_feb-2024 -
1 28-feb-2024 x
1 10-mar-2024 -
1 20-Mar-2024 x
1 31-mar-2024 -
1 01-apr-2024 x
2 1-feb-2024 x
2 28-feb-2024 -
2 10-mar-2024 -
2 20-Mar-2024 -
2 31-mar-2024 x
2 10-apr-2024 x
2 30-apr-2024 -
Output--Last populated value of the parameter prior to the month end.
So if I run my code as of Feb-2024 month end, the output should be
ID Date Parameter
1 28-feb-2024 x
2 28-feb-2024 x
So if I run my code as of Mar-2024 month end, the output should be
ID Date Parameter
1 31_mar-2024 x
2 31-mar-2024 x
So if I run my code as of Apr-2024 month end, the output should be
ID Date Parameter
1 1-Apr-2024 x
2 30-apr-2024 x
I tried the below, but it only gives me the last row, without considering month
data output;
update input(obs=0) input;
by ID;
data output;
update input(obs=0) input;
by ID; run;
data have;
input ID Date :date11. Parameter $;
format Date date11.;
if Parameter='-' then call missing(Parameter);
cards;
1 1-feb-2024 -
1 28-feb-2024 x
1 10-mar-2024 -
1 20-Mar-2024 x
1 31-mar-2024 -
1 01-apr-2024 x
2 1-feb-2024 x
2 28-feb-2024 -
2 10-mar-2024 -
2 20-Mar-2024 -
2 31-mar-2024 x
2 10-apr-2024 x
2 30-apr-2024 -
;
%let month= Apr2024 ;
data want;
set have(rename=(Parameter=P) where=(date<=%sysfunc(intnx(month,"01&month."d,0,e))));
by id;
length Parameter $ 80;
retain Parameter;
if first.id then call missing(Parameter);
if not missing(P) then Parameter=P;
if last.id;
drop P;
run;
data have;
input ID Date :date11. Parameter $;
format Date date11.;
if Parameter='-' then call missing(Parameter);
cards;
1 1-feb-2024 -
1 28-feb-2024 x
1 10-mar-2024 -
1 20-Mar-2024 x
1 31-mar-2024 -
1 01-apr-2024 x
2 1-feb-2024 x
2 28-feb-2024 -
2 10-mar-2024 -
2 20-Mar-2024 -
2 31-mar-2024 x
2 10-apr-2024 x
2 30-apr-2024 -
;
%let month= Apr2024 ;
data want;
set have(rename=(Parameter=P) where=(date<=%sysfunc(intnx(month,"01&month."d,0,e))));
by id;
length Parameter $ 80;
retain Parameter;
if first.id then call missing(Parameter);
if not missing(P) then Parameter=P;
if last.id;
drop P;
run;
Under the assumption that you've got always at least one populated value per ID and month below variation to what @Ksharp proposed will only select the chosen month for analysis instead of all months up to the chosen month.
%let month= Feb2024 ;
data want;
set have(rename=(Parameter=P) where=(intck('month',"01&month."d,date)=0 ));
by id;
length Parameter $ 80;
retain Parameter;
if first.id then call missing(Parameter);
if not missing(P) then Parameter=P;
if last.id;
drop P;
run;
Why did you include data after the stop date???
Assuming the hyphens in your listing are supposed to be MISSING values then your data looks something like this one:
data have;
input ID Date :date. Parameter $ ;
format date date9.;
cards;
1 1_feb-2024 .
1 28-feb-2024 x
1 10-mar-2024 .
1 20-Mar-2024 x
1 31-mar-2024 .
1 01-apr-2024 x
2 1-feb-2024 x
2 28-feb-2024 .
2 10-mar-2024 .
2 20-Mar-2024 .
2 31-mar-2024 x
2 10-apr-2024 x
2 30-apr-2024 .
;
So you only want to consider the data before the start of MARCH if want the data as of the end of FEBRUARY.
data want;
update have(obs=0) have;
by id;
where date < '01MAR2024'd ;
run;
Result
Obs ID Date Parameter 1 1 28FEB2024 x 2 2 28FEB2024 x
To find the data as of some other point in time just change the date used in the WHERE statement.
/*
Yes. You can,
But it is very tough task for PROC SQL.
Pick up the right tool (data step) for this kind of task.
*/
data have;
input ID Date :date11. Parameter $;
format Date date11.;
if Parameter='-' then call missing(Parameter);
cards;
1 1-feb-2024 -
1 28-feb-2024 x
1 10-mar-2024 -
1 20-Mar-2024 x
1 31-mar-2024 -
1 01-apr-2024 x
2 1-feb-2024 x
2 28-feb-2024 -
2 10-mar-2024 -
2 20-Mar-2024 -
2 31-mar-2024 x
2 10-apr-2024 x
2 30-apr-2024 -
;
%let month= Apr2024 ;
proc sql;
create table want as
select a.*,Parameter
from
(
select distinct id,date from have
where date<=%sysfunc(intnx(month,"01&month."d,0,e))
group by id
having date=max(date)
) as a
left join
(
select distinct id,Parameter from have
where date<=%sysfunc(intnx(month,"01&month."d,0,e)) and Parameter is not missing
group by id
having date=max(date)
) as b
on a.id=b.id
;
quit;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.