Run a macro on all columns

[hhchenfx]

Hi Everyone,

My data has a 1000+ columns with data in the first row. The last available data is data of today (02JAN2022).

So I want to fill the date backward for each row.

Ok, the Macro as shown below produce what I want. However I need 1 line of macro for each variable/column. (This macro is not great yet, I am still working on it).

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

Can you please help me to create a loop code?

Thank you,

HHC

SOLUTION:

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

data have; set have;
date_id=_N_;run;
proc sort data=have; by descending date_id;run;

proc transpose data=have out=want ;
  by descending date_id ;
  var v1-v3;
run;


proc sort data=want; by _NAME_ descending date_id; run;

*Delete all missing row UP TO the first data available;
data want2(drop=_d:);
  set want;
  retain  _del_flg 1;
  by _NAME_;
  if _del_flg or first._NAME_ then 
    do;
      _del_flg=missing(col1);
      if _del_flg then delete;
    end;	run;

*find max date-id for each id;
proc means data=want2 noprint;
by _NAME_;
var date_id;
output out=maxdate Max=Maxdate; run;

proc sql; create table want2 as select a.* , Maxdate from want2 a left join maxdate b on a._NAME_=b._NAME_
order by _NAME_, date_id desc; quit;

data want2; set want2;
format date date9.;
date = today() - (Maxdate - date_id) -1;
run;

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

%let TODAY= '02JAN2022'd;

data output; set _NULL_;run;

%MACRO lastdate( column=);
data subfile; set have;
keep n &column;run;

proc sort data=subfile; by descending n;run;

data first_not_missing; set subfile;
if &column^=.;run;

data first_not_missing; set first_not_missing;
rename n=first_not_missing;
keep n;
if _N_=1;run;

proc sql;
create table subfile as select a.* from subfile a left join first_not_missing on 1=1 WHERE n<=first_not_missing
order by n desc;quit;

data lastdate; set subfile; 
keep n; rename n=lastdate;
if _N_=1;run;

proc sql;
create table subfile as select * from subfile left join lastdate on 1=1;quit;

data subfile; length variable_name $8.; set subfile;
format datadate date10.;
rename &column=value;
keep &column datadate variable_name;
datadate=&today - (lastdate - n);
variable_name=vname(&column);
run;

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

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

[Tom]

Can you please explain WHAT you are trying to accomplish?

What does "fill the date backward" mean?

I cannot figure out what the heck that macro is trying to do.

What is the output you want from your example input data?

[Tom]

You are just trying to add an ascending DATE variable?

data add_date;
  format date yymmdd10.;
  set have nobs=nobs;
  date=today()-nobs+n;
run;

Result

Obs          date    n    v1    v2    v3

 1     2022-02-19    1    33    44     1
 2     2022-02-20    2     5     6     5
 3     2022-02-21    3     4     5     7
 4     2022-02-22    4     .     7     .
 5     2022-02-23    5    66     4     .
 6     2022-02-24    6     .     9     .

[hhchenfx]

Hi Tom,

The output I want is produced by this macro.

The last date is 02JAN2022

I am still working on making the macro efficient 😉

HHC

Obs variable_name value datadate 
1 v1 66 02JAN2022 
2 v1 . 01JAN2022 
3 v1 4 31DEC2021 
4 v1 5 30DEC2021 
5 v1 33 29DEC2021 

6 v2 9 02JAN2022 
7 v2 4 01JAN2022 
8 v2 7 31DEC2021 
9 v2 5 30DEC2021 
10 v2 6 29DEC2021 

11 v2 44 28DEC2021 
12 v3 7 02JAN2022 
13 v3 5 01JAN2022 
14 v3 1 31DEC2021 
 

[Tom · Accepted solution]

Why is this not a simple PROC TRANSPOSE application?

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


data add_date / view=add_date;
  format date yymmdd10.;
  set have nobs=nobs;
  date=today()-nobs+n;
run;

proc transpose data=add_date out=want ;
  by date ;
  var v1-v3;
run;

proc sort;
  by _name_ date;
run;

proc print data=want;
run;

Results

Obs          date    _NAME_    COL1

  1    2022-02-19      v1       33
  2    2022-02-20      v1        5
  3    2022-02-21      v1        4
  4    2022-02-22      v1        .
  5    2022-02-23      v1       66
  6    2022-02-24      v1        .
  7    2022-02-19      v2       44
  8    2022-02-20      v2        6
  9    2022-02-21      v2        5
 10    2022-02-22      v2        7
 11    2022-02-23      v2        4
 12    2022-02-24      v2        9
 13    2022-02-19      v3        1
 14    2022-02-20      v3        5
 15    2022-02-21      v3        7
 16    2022-02-22      v3        .
 17    2022-02-23      v3        .
 18    2022-02-24      v3        .

[hhchenfx]

I am so sorry for not making thing clear.

I got my code right and will report.

Thanks,

HHC

[Tom]

Does not appear to be any need to "macro" solution for this problem.

It should be simple to adjust the PROC TRANSPOSE code to use different names for the variables.

Easy to adjust the date calculations to use a different ending date.

You could change the PROC SORT to list the data by descending date if you want.

You could then add an extra step to remove the trailing (now leading) missing values.

data fixed;
  set want;
  by _name_ descending date ;
  if first._name_ then found =0;
  retain found;
  if not missing(col1) then found=1;
  if found then output;
  drop found;
run;

[hhchenfx]

You are right, Tom.

I should do transpose and work from there!

Thanks,

HHC

[hhchenfx]

Here is mine final one

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

data have; set have;
date_id=_N_;run;
proc sort data=have; by descending date_id;run;

proc transpose data=have out=want ;
  by descending date_id ;
  var v1-v3;
run;


proc sort data=want; by _NAME_ descending date_id; run;

*Delete all missing row UP TO the first data available;
data want2(drop=_d:);
  set want;
  retain  _del_flg 1;
  by _NAME_;
  if _del_flg or first._NAME_ then 
    do;
      _del_flg=missing(col1);
      if _del_flg then delete;
    end;	run;

*find max date-id for each id;
proc means data=want2 noprint;
by _NAME_;
var date_id;
output out=maxdate Max=Maxdate; run;

proc sql; create table want2 as select a.* , Maxdate from want2 a left join maxdate b on a._NAME_=b._NAME_
order by _NAME_, date_id desc; quit;

data want2; set want2;
format date date9.;
date = today() - (Maxdate - date_id) -1;
run;

[ballardw]

How about providing an example of the expected data set.

I get lazy this time of day and don't feel like attempting to parse code that the author indicates may not be doing what is needed. (If it did do what was needed would there be a question?)

[Reeza]

Assuming your code works and does what you need here's how to use CALL EXECUTE to run it for all variables in your data set. I'm assuming you don't want it to run for the variable N which you didn't state as a requirement but you can change the code as needed. 

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;

Spoiler

---

@hhchenfx wrote:

Hi Everyone,

My data has a 1000+ columns with data in the first row. The last available data is data of today (02JAN2022).

So I want to fill the date backward for each row.

Ok, the Macro as shown below produce what I want. However I need 1 line of macro for each variable/column. (This macro is not great yet, I am still working on it).

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 n v1 v2 v3;
datalines;
1 33 44 1
2 5 6 5
3 4 5 7
4 . 7 .
5 66 4 .
6 . 9 .
;run;

%let TODAY= '02JAN2022'd;

data output; set _NULL_;run;

%MACRO lastdate( column=);
data subfile; set have;
keep n &column;run;

proc sort data=subfile; by descending n;run;

data first_not_missing; set subfile;
if &column^=.;run;

data first_not_missing; set first_not_missing;
rename n=first_not_missing;
keep n;
if _N_=1;run;

proc sql;
create table subfile as select a.* from subfile a left join first_not_missing on 1=1 WHERE n<=first_not_missing
order by n desc;quit;

data lastdate; set subfile; 
keep n; rename n=lastdate;
if _N_=1;run;

proc sql;
create table subfile as select * from subfile left join lastdate on 1=1;quit;

data subfile; length variable_name $8.; set subfile;
format datadate date10.;
rename &column=value;
keep &column datadate variable_name;
datadate=&today - (lastdate - n);
variable_name=vname(&column);
run;

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

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

---