topic Re: Retain the last data until the non empty row in SAS Programming

Retain the last data until the non empty row

SASdevAnneMarie — Sun, 10 Mar 2024 16:36:18 GMT

Hello Experts,

I would like to replace the empty rows with the last known data and create the column MT_CORR, but my code doesn't work.

My code is :

Data matable3;
	set matable2;
	by Numero Code2;
	if first.Numero and first.Code then 
	MT_CORR=MT_2;
    retain MT_CORR;
	;
run;

I have joined the file.

Thank you for you help.

Re: Retain the last data until the non empty row

mkeintz — Sun, 10 Mar 2024 16:47:44 GMT

Do selective updating of mt_corr, then selective updating of mt_2. This program is untested, but it should fill in the yellow cells in your data:


data matable3;
  set matable2;
  by numero code2;
  retain mt_corr;
  if first.code2 then mt_corr=.;
  if mt_2^=. then mt_corr=mt_2;
  else mt_2=mt_corr;
run;

Re: Retain the last data until the non empty row

Tom — Sun, 10 Mar 2024 17:09:15 GMT

Just use the UPDATE statement.

If you want to carry forward all of the variables then use:

data matable3;
  update matable2(obs=2) matable2;
  by numero code2;
  output;
run;

If there are some that you don't want the missing values replaced with the previous value then re-read those variables.

data matable3;
  update matable2(obs=2) matable2;
  by numero code2;
  set matable2(keep=other);
  output;
run;

PS No need (or desire) to post on XLSX file to share example data. A simple data step is easier to create and very much easier for others to use to create your example data.

data have;
  input numero $ code2 $ date :yymmdd. mt_2 expect;
  format date yymmdd10.;
cards;
CC1 X 2019-07-18 204167.73653 204167.73653
CC1 X 2019-08-06 . 204167.73653
CC1 X 2019-08-06 . 204167.73653
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-12-10 313986.57001 313986.57001
CC1 X 2019-12-10 313986.57001 313986.57001
CC1 X 2019-12-31 339560.10139 339560.10139
CC1 X 2019-12-31 . 339560.10139
CC1 X 2019-12-31 . 339560.10139
PPK LT 2019-07-18 5000.7365312 5000.7365312
PPK LT 2019-08-06 . 5000.7365312
PPK LT 2019-08-06 . 5000.7365312
PPK LT 2019-09-30 . 5000.7365312
PPK LT 2019-12-31 . 5000.7365312
PPK LT 2019-12-31 7000.1013906 7000.1013906
PPK LT 2019-12-31 7050.1013906 7050.1013906
;