topic Re: Run a macro on all columns - Repost in SAS Programming

Run a macro on all columns - Repost

hhchenfx — Fri, 25 Feb 2022 01:23:25 GMT

Hi Everyone,

My data has a 1000+ columns. The LAST available data in a column is data of today().(So in the code, I sort descending date_id to bring the today up to top). Note, after the last available data row, there could be no value rows.

Based on the last data is today(), I need to fill date for each row.

Ok, the Macro as shown below produce the exact output I want.

However I need 1 line of macro for each variable/column.

Clearly, I need to loop through all columns in my data.

Can you please help me to create a loop code?

Thank you,

HHC

data have; 
input date_id v1 v2 v3;
datalines;
1 33 44 1
2 5 6 5
3 4 5 .
4 . 7 .
5 66 4 6
6 . 9 .
;run;
data output; set _NULL_;run;

%MACRO lastdate( column=);

*create subfile with only: column date_id and 1 variable;
data subfile; set have;
keep date_id &column;
run;

*sort to bring the most recent date on top;
proc sort data=subfile; by descending date_id;run;

*Delete all missing row UP TO the first data available which is today;
data subfile(drop=_:);
  set subfile;
  retain _del_flg 1;
  if _del_flg then 
    do;
      _del_flg=missing(&column);
      if _del_flg then delete;
    end;
run;

*Fill Date;
data subfile; length variable_name $8.;set subfile;
keep &column variable_name datadate;
rename &column=value;
variable_name=vname(&column);

format datadate date10.;
if _N_=1 then  lastdate=date_id;
retain lastdate;
datadate=today() - (lastdate-date_id);

run;

*Add to output file;
data output; set output subfile; run;
%mend;

%lastdate( column=v1);
%lastdate( column=v2);
%lastdate( column=v3);

Re: Run a macro on all columns - Repost

Reeza — Fri, 25 Feb 2022 01:25:08 GMT

I posted the answer in your previous question.


ods select none;
proc contents data=have out=var_list (keep = name);
run;
ods select all;

data execute_all;
set var_list;
where name ne 'n';

str = catt('%lastdate(column=', name, ');');
call execute(str);
run;

Re: Run a macro on all columns - Repost

hhchenfx — Fri, 25 Feb 2022 01:28:12 GMT

Thank you for helping!

You save my day.

HHC

Re: Run a macro on all columns - Repost

Tom — Fri, 25 Feb 2022 02:35:48 GMT

The macro still looks convoluted and confusing.

It seems you are trying to do this:

%macro lastdate(column=);
* Sort by descending DATE_ID ;
proc sort data=have(keep=date_id &column rename=(&column=value)) out=subfile;
  by descending date_id;
run;

*Delete all missing row UP TO the first data available which is today;
data subfile;
  length variable_name $8;
  retain variable_name "&column" lastdate;
  set subfile;
  retain _del_flg 1;
  if _del_flg then do;
     if missing(value) then delete;
     lastdate=date_id;
     _del_flg=missing(value);
  end;
  datadate=today() - (lastdate-date_id);
  format datadate date9.;
  keep variable_name datadate value;
run;

*Add to output file;
proc append base=output data=subfile force;
run;
%mend;

So given a HAVE dataset like this:

data have;
  input date_id v1-v3;
datalines;
1 33 44 1
2 5 6 5
3 4 5 7
4 . 7 .
5 66 4 .
6 . 9 .
;

If you want to run it for every variable except DATA_DATE then you could use something like this:

proc delete data=output; run;
proc transpose data=have(drop=date_id obs=0) out=names; run;
data _null_;
  set names;
  call execute(cats('%nrstr(%lastdate)(column=',_name_,')'));
run;

Results

       variable_
Obs      name       value     datadate

  1       v1          66     24FEB2022
  2       v1           .     23FEB2022
  3       v1           4     22FEB2022
  4       v1           5     21FEB2022
  5       v1          33     20FEB2022
  6       v2           9     24FEB2022
  7       v2           4     23FEB2022
  8       v2           7     22FEB2022
  9       v2           5     21FEB2022
 10       v2           6     20FEB2022
 11       v2          44     19FEB2022
 12       v3           7     24FEB2022
 13       v3           5     23FEB2022
 14       v3           1     22FEB2022

Re: Run a macro on all columns - Repost

hhchenfx — Mon, 28 Feb 2022 00:41:20 GMT

Thanks Tom.

I will try your method.

HHC