This is how I would do it, less reading of datasets.
data test;
input ID $ bp weight bc form_number;
datalines;
1 120 78 178 1
1 134 80 177 2
1 143 . 176 3
2 111 58 . 1
3 154 55 160 1
3 178 56 144 2
;
run;
%macro locf(dsin=, dsout=);
*get the latest value;
data latest_values;
update &dsin(obs=0) &dsin;
by id;
run;
*build a rename list for variable names;
proc sql noprint;
select catx("=", name, catt('LAST_', upper(name)))
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='LATEST_VALUES'
and upper(trim(name)) ne 'ID';
quit;
*merge in final results;
data &dsout;
merge &dsin latest_values (rename = (&rename_list));
by id;
run;
*remove latest values dataset for clean process;
proc sql;
drop table latest_values;
quit;
%mend;
%locf(dsin=test, dsout=want);
As per the log, you need to keep ID as well, because it's in the BY statement. You also need to KEEP the variables on both datasets, i.e.:
data latest_values;
update &dsin(keep=id bp weight obs=0) &dsin (keep=id bp weight);
by id;
Glad it worked. It would be better to accept @Reeza's answer with the macro definition as the correct answer. My comment here was just a small addition.
You are right. Thank you both!
If you click on the hamburger icon for my answer, I think it might give you an option "un-mark as correct", "this is not the solution" or something to that effect.
I was thinking statement, not data set option to keep it cleaner? But probably more efficient as a data set option though. Glad you got it sorted.
data latest_values;
update &dsin( obs=0) &dsin ;
by id;
keep id bp weight;
run;
Hello, the code you provided works perfectly but today I tried using a dataset that has really long variable names (more than 32 characters). I get the error: Variable name is longer than 32 characters. We do not want to change the variable names. Is there a way to increase the length of the variables in the macro. We have both numeric and character variables. Any advice is greatly appreciated.
Thanks.
If your source dataset has variables with names longer than 32-5 bytes then you cannot add LAST_ prefix and keep the full original variable name since the result will be more than 32 bytes.
You might be able to just truncate to 32 bytes.
newname=substr('last_'||name,1,32);
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.