BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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        .

View solution in original post

10 REPLIES 10
Tom
Super User Tom
Super User

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
Super User Tom
Super User

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
Barite | Level 11

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
Super User Tom
Super User

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
Barite | Level 11

I am so sorry for not making thing clear.

I got my code right and will report.

Thanks,

HHC

Tom
Super User Tom
Super User

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
Barite | Level 11

You are right, Tom.

I should do transpose and work from there!

Thanks,

HHC

hhchenfx
Barite | Level 11

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
Super User

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
Super User

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

 


sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1460 views
  • 1 like
  • 4 in conversation