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.
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;
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;
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 ;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.