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); ... View more

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);   ... View more

Hi Everyone, My data have few first rows with missing data. How can I delete them? There are other missing but inside the good data and I need to keep those missing. In the data below, I want to delete row row 1 and 2.   Thank you, HHC data have; input n v1; datalines; 1 . 2 . 3 4 4 66 5 . 6 66 7 3 8 . 9 8 ;run;   ... View more

Hi, I want to find Epoch milisecond for a list of date at 6:00AM GMT. I am not sure how to do it and can you please help? Many thanks, HHC https://www.epochconverter.com/  Date and time (GMT): Monday, May 31, 2021 6:00:00 AM Timestamp in milliseconds: 1622440800000 Date and time (GMT): Sunday, May 30, 2021 6:00:00 AM Timestamp in milliseconds: 1622354400000   data have; input name $ date:mmddyy10.; format date mmddyy10.; datalines; 05/31/2021 05/30/2021 ;   ... View more

Hi, For a give day, I lookback 2 day and look forward 3 day and min value and the date of that min value occurs. My code below seems to get the job done but it give error in log as below. How can I fix it? Thanks, HHC 12744 12745 data want; 12746 set have; 12747 min=value; 12748 min_date=date; 12749 do n = _N_-2 to _N_+3 by 1; 12750 set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n; 12751 if id_1 = id and value_1<min then do; 12752 min=value_1; 12753 min_date=date_1; 12754 end; 12755 end; 12756 run; id=1 date=1 value=1 min=1 min_date=1 n=5 id_1=1 date_1=4 value_1=8 _ERROR_=1 _N_=1 id=1 date=2 value=3 min=1 min_date=1 n=6 id_1=1 date_1=5 value_1=9 _ERROR_=1 _N_=2 id=2 date=6 value=80 min=20 min_date=7 n=18 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=14 id=2 date=7 value=20 min=20 min_date=7 n=19 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=15 id=2 date=8 value=60 min=20 min_date=7 n=20 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=16 NOTE: There were 16 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WANT has 16 observations and 8 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.01 seconds     data have; input id date value; datalines; 1 1 1 1 2 3 1 3 4 1 4 8 1 5 9 1 6 8 1 7 2 1 8 6 2 1 10 2 2 30 2 3 40 2 4 80 2 5 90 2 6 80 2 7 20 2 8 60 ;run; data want; set have; min=value; min_date=date; do n = _N_-2 to _N_+3 by 1; set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n; if id_1 = id and value_1<min then do; min=value_1; min_date=date_1; end; end; run;   ... View more

Hi, I want to find record that: v1< min (v2,v3,v4.... vn). Is there way rather than listing all v? Thanks  HHC data a; input id v1 v2 v3 v4 v5; datalines; 1 4 5 6 7 8 2 5 9 10 85 3 3 99 1 2 3 4 ;run; data b; set a; if v1< min(v2-v5); *SAS take it as minus; run; ... View more