DATA Step, Macro, Functions and more

Max date value

Reply
Contributor
Posts: 69

Max date value

Hi,

I am having the columns as AS_OF_DT and dw_Load_time as date columns and the format is numeric.

I need to take the latest of these date values.

AS_OF_DT  DW_LOad_time

19601  19614.462789

19589  19614.462789

19235  19603.345679

19590  19405.213453

19560  19349.298756

19340  18777.132456

19430  18796.654378

I need to refer these in the codes as macro variables.

Could any one help me on how to go on this.

Regards,

Sid

Trusted Advisor
Posts: 1,228

Re: Max date value

Posted in reply to Siddhartha

proc sql noprint;
select max(AS_OF_DT), max(DW_LOad_time) into :max_date, :max_time from have;
quit;

%put &max_date &max_time;

Contributor
Posts: 69

Re: Max date value

I was unable to get the values  in correct format i.e., DW_Load_time should come as datetime format.

Regards,

Sid

Regular Contributor
Posts: 180

Re: Max date value

Posted in reply to Siddhartha


First you need to convert dates into datetimes multiplying by 86400:

data have;
input AS_OF_DT  DW_LOad_time;
format AS_OF_DT  DW_LOad_time datetime.;
as_of_dt=as_of_dt*86400;
dw_load_time=dw_load_time*86400;
cards;
19601  19614.462789
19589  19614.462789
19235  19603.345679
19590  19405.213453
19560  19349.298756
19340  18777.132456
19430  18796.654378
;
run;

proc sql noprint;
select max(AS_OF_DT) as max_DT format datetime., max(DW_LOad_time) as Max_DW_Load_time format datetime. into :max_date, :max_time from have;
quit;
%put &max_date &max_time;

CTorres

Trusted Advisor
Posts: 1,228

Re: Max date value

Posted in reply to Siddhartha

As suggested by , to get DW_Load_time as datetime format  first convert DW_Load_time into seconds then apply datetime format.

Super User
Posts: 19,815

Re: Max date value

Posted in reply to Siddhartha

Assuming your load time is actually a date and your data provided is incorrect:

proc sql noprint;

select max(put(AS_OF_DT, date9.)), max(put(DW_LOad_time, datetime21.)) into :max_date, :max_time from have;

quit;

%put &max_date &max_time;

Assuming your load time is actually a date and you need to convert it to datetime

proc sql noprint;

select max(put(AS_OF_DT, date9.)), max(put(dhms(DW_LOad_time,0,0,0), datetime21.)) into :max_date, :max_time from have;

quit;

%put &max_date &max_time;

Ask a Question
Discussion stats
  • 5 replies
  • 1855 views
  • 0 likes
  • 4 in conversation