We were not over by much. Using P_ solved it! Thanks again.
For most of this, macros are not really needed. This is a program that automatically adapts to the variables you have, except for determining names for the new variables. That part you have to add yourself.
data want;
do until (last.id);
update have (obs=0) have;
by id;
end;
*** The next 3 lines are being hard coded. If you are clever, it can be automated;
late_bp = bp;
late_weight = weight;
late_bc = bc;
do until (last.id);
set have;
by id;
output;
call missing (of late_:);
end;
run;
The code is untested, so post again if something isn't working for you.
I like @Astounding's approach (and wish I could write code like that without testing it : ).
As mentioned, the block of code to create the late_ variables could be automated with a macro. Since the variable names you want to LOCF will differ, it's helpful if you have a utility macro that will give you a list of the variables in a dataset, something like:
%macro VarList(data) ;
%local rc varnames ;
%let rc = %sysfunc(dosubl(%nrstr(
proc contents data=&data
out=__VarList(keep=name) noprint ;
run ;
proc sql noprint ;
select name into :varlist separated by ' '
from __VarList ;
drop table __VarList ;
quit ;
)));
&varlist /*return*/
%mend ;
Then you can use that macro to build a list of the variables that need to be locf'd. In below macro, instead of generating an assignment statement to create each last_ variable, I used a RENAME option on the UPDATE statement.
%macro locf(data=
,out=
,by=id
,nolocf=form_number
) ;
%local
varlist /*list of variables in data that will be locf*/
renamelist /*list of renames to add last_ prefix */
i
vari
;
%*build rename list ;
%let varlist=%varlist(&data(drop=&by &nolocf)) ;
%do i=1 %to %sysfunc(countw(&varlist,%str( ))) ;
%let vari=%scan(&varlist,&i,%str( )) ;
%let renamelist=&renamelist &vari=last_&vari ;
%end ;
%*put &=varlist &=renamelist ;
data &out;
do until (last.%sysfunc(scan(&by,-1,%str( ))));
update &data (rename=(&renamelist) obs=0) &data(rename=(&renamelist) );
by &by;
end;
do until (last.%sysfunc(scan(&by,-1,%str( ))));
set &data;
by &by;
output;
call missing (of last_:);
end;
run;
%mend locf ;
Test like:
data have ;
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;
%locf(data=have,out=want)
proc print data=want ;
run ;
That said, I agree with @Reeza also. The structure of the input data is not weird. It's great that the input data is in a vertical format. The output structure you want is unusual, where you essentially merge the results of LOCF back onto the data but only for the first record of each ID.
If you want the last non-missing value per variable then treat the whole dataset as a series of transactions by using the UPDATE statement.
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
;
data want;
update test(obs=0) test;
by id;
run;
Which would result in:
form_ Obs ID bp weight bc number 1 1 143 80 176 3 2 2 111 58 1 3 3 178 56 144 2
If you need to do it for multiple datasets then get the list of datasets into a dataset and use that to generate the code.
data datasets;
input input :$41. output :$41.;
cards;
work.test work.want
;
filename code temp;
data _null_;
set datasets;
file code;
put 'data ' output ';' / ' update ' input '(obs=0) ' input ';' / ' by id;' / 'run;' ;
run;
%include code / source2;
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.