Can i standardize/convert the data in datetime to have the same "minutes"?

user_5606637 0
Viewed 56

I am a SAS developer using SAS Data Integration Studio.

Currently i have a scenario where data in many excel tabs are having different time value(minutes). For example

Tab A will have datetime
2010-Jan-01 01:00:00
2010-Jan-02 01:00:00
2010-Jan-03 01:00:00

Tab B will have datetime below:
2010-Jan-01 01:25:00
2010-Jan-02 01:25:00
2010-Jan-03 01:25:00

Do take note that table A and table B will have different columns apart from the SAME DATETIME (but not the minutes).

When i merge these 2 tables, i want to ignore the minutes, or rather standardize it to 01:00:00.

Is there a way to do so?

Currently, if i were to merge these 2 tables, i will have "Duplicate"(i mean same day but different record/result due to different minutes).

Imagine in table A, i have a column called Age Table B, i have a column called Net Worth.

When i merge this 2 tables, I am expecting the table to have 1 single row record of 2010-Jan-01 01:00:00 with Age and Net worth.

However, when datetime does not match, they will have a new record with Age containing value but Blank Net worth or likewise.

Seeking for your help on how to achieve that.

sas
2 Answers

You can truncate to the nearest hour using the intnx function before matching:

data _null_;
  mydatetime = datetime();
  mydatehour = intnx('dthour',mydatetime, 0, 'b');
  put mydatetime= is8601dt. / mydatehour= is8601dt.;
run;
user_6674890

There is a function DATEPART(datetime)

Example:

data _null_;
   conn='01feb94:8:45'dt;
   servdate=datepart(conn);
    dhms = dhms(datepart(conn),hour(conn),0,0);
put  dhms datetime.;;
run;

result in log

01FEB94:08:00:00
user_53531770
Related