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);
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 .
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?
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 .
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
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 .
I am so sorry for not making thing clear.
I got my code right and will report.
Thanks,
HHC
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;
You are right, Tom.
I should do transpose and work from there!
Thanks,
HHC
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;
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?)
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;
@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);
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: