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);
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;
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;
Thank you for helping!
You save my day.
HHC
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
Thanks Tom.
I will try your method.
HHC
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.