topic Re: Macro to create new variables with latest values in SAS Programming

Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 20:36:48 GMT

With the following dataset, how do you code a macro that will create variables per ID that reports the values for each variable (bp, weight, bc) that corresponds to the last form_number? And keep the original variable in the dataset? The number of forms per ID may vary.

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;

what we need is the following new dataset:

ID	bp	weight	bc	form_number	late_bp	late_weight	late_bc
1	120	78	178	1	143	80	176
1	134	80	177	2	.	.	.
1	143	.	176	3	.	.	.
2	111	58	.	1	111	58	.
3	154	55	160	1	178	56	144
3	178	56	144	2	.	.	.

Re: Macro to create new variables with latest values

Quentin — Tue, 04 Apr 2023 21:04:22 GMT

This is doable, but it's an unusual structure for data. Is there a particular reason you want this format? What's the next step? If you want to calculate, for example, difference between form 1 value and the last value, you could do that without creating these new variables.

Re: Macro to create new variables with latest values

Seadrago — Tue, 04 Apr 2023 21:06:35 GMT

I think what you are looking for is a macro that gets the last value for each variable per ID. So something like the below:

%macro get_last(var=);

proc sort data=test; by id; run;

data last_&var.;

by id;

set test;

if &var. ne .;

if last.id;

form_number=1;

last_&var.=&var.;

keep id form_number last_&var.;

run;

%mend get_last;

%get_last(var=bp)

%get_last(var=weight)

%get_last(var=bc)

Afterwards you can merge these datasets back into the original TEST by ID FORM_NUMBER. Note: I'm not sure why there is more than 1 FORM_NUMBER. There can only a single last value. Are FORM_NUMBER 2,3,etc meant to be blank in the final dataset?

Re: Macro to create new variables with latest values

ballardw — Tue, 04 Apr 2023 21:16:19 GMT

@ANKH1 wrote:

With the following dataset, how do you code a macro that will create variables per ID that reports the values for each variable (bp, weight, bc) that corresponds to the last form_number? And keep the original variable in the dataset? The number of forms per ID may vary.

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;

what we need is the following new dataset:

ID bp weight bc form_number late_bp late_weight late_bc

1 120 78 178 1 143 80 176

1 134 80 177 2 . . .

1 143 . 176 3 . . .

2 111 58 . 1 111 58 .

3 154 55 160 1 178 56 144

3 178 56 144 2 . . .

You example does not report the values of the last form number as stated. You are showing the value of which ever largest form that has a non-missing value, if any. The 80 for Late_weight for the Id=1 is not from the largest form number 3, but from 2.

So please clarify the statement of your need OR make the example match the stated objective.

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 21:22:02 GMT

Need to create a dataset that reports the last non-missing value per variable per ID. Thanks!

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 21:24:37 GMT

Sorry, each late_var should report the last non-missing value from the all form_numbers per ID.

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 21:25:08 GMT

You are right, it's the last non-missing value per variable. Apologies.

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 21:44:23 GMT

and for the late_var columns there should be one value. I am not sure if it is ok to fill all rows corresponding to the same ID filled out with the same value? Example: for ID 1 the late_bp=143 will be repeated 3 times since this ID has three rows.

Re: Macro to create new variables with latest values

Reeza — Tue, 04 Apr 2023 21:47:29 GMT

Do you really want macro's here? Why?

Arrays are a better idea, and you could automate the naming of the variables if that's the issue.

data last;
set test;
by ID;

array vars(4) bp weight bc form_number;
array last_vars(4) last_bp last_weight last_bc last_form_number;
retain last_bp last_weight last_bc last_form_number;

if first.id then call missing(of last_vars(*));

do i=1 to dim(vars);
if not missing(vars(i)) then last_vars(i) = vars(i);
end;

if last.id then output;

keep id last_:;
run;

data want;
merge test last;
by id;
if not first.id then call missing(of last:);
run;

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 21:49:33 GMT

Thanks! The reason macros are preferred it's because there are many datasets that we want to run.

Re: Macro to create new variables with latest values

Reeza — Tue, 04 Apr 2023 21:52:27 GMT

Well then you definitely need to specify more details. The macro shown here won't really help with many datasets as its designed for multiple variables not multiple data sets. Do the data sets all have the same names? This is kind of a weird structure, would you not want the last on all rows, that's way more common. Or just create a data set with just the last values for other calculations? I suspect there's something missing in the requirements here.

Re: Macro to create new variables with latest values

ANKH1 — Tue, 04 Apr 2023 22:02:40 GMT

not all datasets have the same variables. the only common variables are ID, form_number. The number of datasets are more than 50. I don't understand why this is considered a weird dataset structure. Datasets collect different info from each ID and the number of forms per ID is not constant across datasets either. Since each dataset asks about different topics.

Re: Macro to create new variables with latest values

Astounding — Tue, 04 Apr 2023 22:19:45 GMT

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.

Re: Macro to create new variables with latest values

Reeza — Tue, 04 Apr 2023 22:37:03 GMT

It's an uncommon data structure in that you add the latest to the first row of each ID only.

Whenever I've seen this added it's been added to all rows for that ID or kept in a separate table.

Re: Macro to create new variables with latest values

Reeza — Tue, 04 Apr 2023 22:37:40 GMT

Are there other variables in the data set that you would not get the last value for besides ID?

Re: Macro to create new variables with latest values

Tom — Tue, 04 Apr 2023 23:23:00 GMT

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;

Re: Macro to create new variables with latest values

Quentin — Wed, 05 Apr 2023 13:13:26 GMT

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.

Re: Macro to create new variables with latest values

ANKH1 — Wed, 05 Apr 2023 13:43:40 GMT

Ok, I misunderstood. The adding of the latest to the first row of each ID only is not a requirement. We can add the value to all rows. Thanks.

Re: Macro to create new variables with latest values

ANKH1 — Wed, 05 Apr 2023 13:45:23 GMT

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

Re: Macro to create new variables with latest values

Reeza — Wed, 05 Apr 2023 14:12:28 GMT

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