topic Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum in SAS Programming

can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a column ?

Swapnasis — Tue, 23 Apr 2024 00:38:07 GMT

Input

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;

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Ksharp — Tue, 23 Apr 2024 01:40:33 GMT

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;

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Patrick — Tue, 23 Apr 2024 01:51:25 GMT

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;

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Tom — Tue, 23 Apr 2024 02:12:23 GMT

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.

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Swapnasis — Tue, 23 Apr 2024 03:39:54 GMT

Thanks ksharp.Appreciate it

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Swapnasis — Tue, 23 Apr 2024 03:45:41 GMT

can this logic be written in proc sql, so that I can use the logic to pull data from database?

Re: can someone help write a SAS proc Sql orSAS data step to get the last non blank value in a colum

Ksharp — Tue, 23 Apr 2024 06:04:28 GMT

/*
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;