BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ballardw
Super User

ANYDTDTM and related AnyDate are INFORMATS only. (read data in various inconsistent forms)

For a FORMAT you would want a Format such as DATETIME16. (display values in one consistent manner)

Yves_Boonen
Quartz | Level 8

So the simple example I posted earlier should look like:

 

data dates;
infile 'C:\temp\dates.csv'  dlm=';' truncover firstobs=2 lrecl=500;

length
  date 8
;

format
  date DATETIME16;

informat
  date anydtdtm16.
;

input
  date
;

keep
  date
;

run;

 

This ought to be correct, right? (I will test it right now.)

Yves_Boonen
Quartz | Level 8

Holy tables! It finally worked.

 

I might need to need to get in touch again, if my analysis query starts acting up when it is comparing the dates from the table with a fixed date. I think it should be okay though.

 

Thanks a ton!

 

Smiley Very Happy

Yves_Boonen
Quartz | Level 8

As I said, I would have to come back to this thread, regarding the fixed date comparison.

 

After the third comment in the code below, you will find a big data step. In the middle of the datastep, I will create a variable:

 

"%LET date=01APR2016 00:00:00;"

 

Next up in the same datastep I will calculate if the ACTIVE task ihas crossed his deadline or not. Since an ACTIVE task does not have a close_date, I will need to use a fixed date. The fixed date has to be the date of the day the unload was created and that's why it has to be the 1st of April 2016. Since the above quest to importing the dates resulted in a format of "ddMMMyyyy hh:mm:ss", I thought I could just write t he date variable like I did above. The result is not what I expected. When I run the entire query below, everything works as intended, but I have 0 active_overtime tasks for all users in the table. There are no errors, but I do think SAS does not recognize the format of the variable date. Could I just use the same strategy as the solution above? Simply add a format or informat in the %LET command?

 

/* Sorteer op intake gesprek. */

proc sql;
create table task_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where object_name = 'Intake gesprek' 
order by task_handler;
quit;

/* Sorteer op status ACTIVE en DONE. */

proc sql;
create table task_2 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from task_1
where task_state in ('ACTIVE','DONE')
order by task_handler, task_state;
quit;

/* Alle ACTIVE en DONE taken optellen en overtime datum berekenen (ACTIVE_OVERTIME en DONE_OVERTIME). */

data task_3;
set task_2;
by task_handler;
retain
	active
	active_overtime
	done
	done_overtime
;

if first.task_handler
then do;
  	active=0;
	active_overtime=0;
  	done=0;
	done_overtime=0;
end;

/* Sinds er unloads worden gebruikt, wordt er geen live data gebruikt. Variabele "date" is een referentie datum, die overeen komt met de datum van de unloads. */

%LET date=01APR2016 00:00:00;

select (task_state);

  	when ('DONE') do;
    	done+1;
    	if close_date > deadline then done_overtime+1;
  	end;

	when ('ACTIVE') do;
    	active+1;
    	if date() > deadline then active_overtime+1;
  	end;

  	otherwise ignore+1;
end;

if last.task_handler then output;
keep task_handler active active_overtime done done_overtime;
run;

/* Sorteer op status CANCEL en CANCELLED. */

proc sql;
create table cancel_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where task_state in ('CANCEL','CANCELLED')
order by task_handler, task_state;
quit;

/* Alle CANCEL en CANCELLED taken optellen. */

proc sql;
create table cancel_2 as
select task_handler, count(*) as total_cancel
from cancel_1
group by task_handler;
quit;

/* Sorteer op status WAITING. */

proc sql;
create table waiting_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where task_state in ('WAITING')
order by task_handler, task_state;
quit;

/* Alle WAITING taken optellen. */

proc sql;
create table waiting_2 as
select task_handler, count(*) as total_waiting
from waiting_1
group by task_handler;
quit;

/* Alle bovenstaande tabellen samenvoegen tot het eindresultaat. (ACTIVE, DONE, CANCEL en WAITING.) */

proc sql;
create table report_1 as
select A.object_name, A.group_members, B.active, B.active_overtime, B.done, B.done_overtime
from unload.employee as A, task_3 as B
where A.group_members=B.task_handler
;
quit;

proc sql;
create table report_2 as
select A.object_name, A.group_members, A.active, A.active_overtime, A.done, A.done_overtime, B.total_cancel as cancelled
from report_1 as A, cancel_2 as B
where A.group_members=B.task_handler
;
quit;

proc sql;
create table report.task as
select A.object_name, A.group_members, A.active, A.active_overtime, A.done, A.done_overtime, A.cancelled, B.total_waiting as waiting
from report_2 as A, waiting_2 as B
where A.group_members=B.task_handler
;
quit;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, first off, you never use the macro variable &DATE. in your code, is it supposed to be in this line?

    	if date() > deadline then active_overtime+1;

If so then you want something like:

    	if "&date."d > deadline then active_overtime+1;

 Remember its a macro variable so &<macro variable name>. is the proper use.  What you did was invoke the function date() which returns todays date.But why put that date in a macro variable at all?  Just put the date in where you need it, its only one occurence.

 

Also, I am now finished work, so not going through the whole code, but it seems very verbose.  The first two steps could be condensed to:

data task1 task2;
  set unload.task (keep=(task_handler object_name task_state deadline start_date close_date r_creation_date));
  if object_name="Intake gesprek" then output task1;
  if object_name="Intake gesprek" and task_state in ("ACTIVE","DONE") then output task2;
run;

Just for an example.

 

 

 

Yves_Boonen
Quartz | Level 8

The only part of the code that is important right now, is this part:

 

%LET date=01APR2016 00:00:00;

select (task_state);

  	when ('DONE') do;
    	done+1;
    	if close_date > deadline then done_overtime+1;
  	end;

	when ('ACTIVE') do;
    	active+1;
    	if "&date."d > deadline then active_overtime+1;
  	end;

  	otherwise ignore+1;
end;

if last.task_handler then output;
keep task_handler active active_overtime done done_overtime;
run;

 

As you can see, I have changed the "date() > deadline..." to ""&date."d > deadline..." instead. However, the column active_overtime still returns a column with only 0 values. Since I make compare the fixed date to the deadline variable from task2, I checked the data in said table. The deadline column holds data that should not return a 0 after the calculation, so my only assumption would be that the calculation goes wrong.

 

These are the first 20 rows of the deadline column in task2:

 

deadline
 
 
31MAR16:09:22:00
09APR16:11:44:00
07APR16:17:01:00
22MAR16:16:27:00
21MAR16:09:03:00
07APR16:10:23:00
07APR16:14:41:00
05APR16:09:11:00
07APR16:15:02:00
07APR16:10:17:00
30MAR16:11:49:00
08APR16:10:34:00
09APR16:12:07:00
08APR16:14:33:00
 
 
 
 

 

The first 2 dates and the last 4 dates are from a task with the state DONE. Since the calculation with "&date."d goes for ACTIVE tasks only, I emptied those cells. As you can see, there are some tasks with a deadline in March, which means they should return a +1 to active_overtime. Am I missing something?

Kurt_Bremser
Super User
%LET date=01APR2016 00:00:00;

is the string of a datetime constant. When using it with "&date"d, you implicitly get a date constant, which is (for dates after 01jan1960) considerably smaller than any datetime values, as those count seconds.

From your list, I guess that deadline is a datetime value, so you should use "&date"dt in your comparison.

Yves_Boonen
Quartz | Level 8

Thanks again Kurt and RW9. You both helped me get there.

Tom
Super User Tom
Super User

I few issues to make your import code simpler.

The LENGTH statement defines how SAS stores the values. SAS only has two data types, fixed length character strings and floating point numbers. Note that date, time and datetime values are numeric.  You should set the length of numeric variables to 8.

 

You don't need to attach FORMAT or INFORMAT to character variables, SAS already knows how to read and write character varaibles.  The only reason might be if you wanted to preserve leading spaces in the values you could attach the $CHAR format and informat.  But I seriously doubt that you want to preserve leading spaces in OBJECT_NAME or your other character variables.

 

ANYDTDTM. is an INFORMAT.  It attempts to figure out on a value by value basis what date time format the input text is in so that it can convert it to an actual datetime value.  To display the values you need to use a specific datatime format such as DATETIME20. or if you don't want to see the time part you could use DTDATE9. 

 

You don't need to code a KEEP statement in your datastep since you are just listing all of the variables.

 

You can simplify your INPUT statement by using a varaible list.  You need to be careful to define the variables in the same order in your datastep as you want to read them from the text file.  

 

data unload.task;
  infile 'C:\temp\JNX_unload_tasks_ 201603_.csv' dlm=';' truncover firstobs=2 lrecl=500 dsd;
  length
    r_object_id $16
    object_name $70
    deadline 8
    start_date 8
    close_date 8
    task_handler $25
    task_state $9
    dossier_nr $6
    sequence $1
    r_creation_date 8
  ;
  informat deadline start_date close_date r_creation_date anydtdtm.;
  format deadline start_date close_date r_creation_date datetime20.;
  input r_object_id -- r_creation_date ;
run;

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
  • 24 replies
  • 2583 views
  • 12 likes
  • 5 in conversation