Macro to create new variables with latest values

[ANKH1]

Yes, the last value is required for a selection of variables per dataset.

[Reeza · Accepted solution]

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);

[ANKH1]

Thanks! The output is a dataset with all variables with the last_prefix (except for ID). What if we want to only do this for not all variables in the dataset? Can this to be added to the macro?

[Reeza]

Add that into the "update" step with latest via a KEEP or DROP statement, whichever makes the most sense for your data.

[ANKH1]

I added the following and didn't work.
data latest_values;
update &dsin(obs=0) &dsin (keep=bp weight);
by id;
ERROR: BY variable ID is not on input data set test.
ERROR: UPDATE statement needs a BY statement.
run;

[Quentin]

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;

[ANKH1]

Thank you! This worked perfectly.

[Quentin]

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.

[ANKH1]

You are right. Thank you both!

[Quentin]

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.  

[ANKH1]

Thanks! I edited my answer once I found the hamburger icon and I guess you were just typing what I needed while I was updating my reply. Thank you again!

[Reeza]

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;

[ANKH1]

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.

[Tom]

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);

[Reeza]

SAS has a hard limit on the length of variable names, 32 characters. If you're going to work with this data in SAS significantly, change the length of the variable name.