I have a huge CSV-file which I import as a whole to my unload library in SAS. In the import script I use format datetime16. and I use informat anydtdtm16. for every column containing dates.
If i check the table in the unload library, the CSV-file is imported correctly and the dates are in the format assigned above.
Next up I will use proc sql to filter out the data I don't need and to create a clean table only containing the information needed for this assignement. During this proc sql I also assign a format datetime16. to r_creation_date and when I check the clean table, the dates are in the formats required.
As soon as I use your part of the script to do the calculations, the dates end up in a numerical format.
Below you will find the entire code. The only thing I need to manage, is to maintain dates and not a numerical variable.
/* Collect all AVE documents in state HOLD and TODO */
proc sql;
create table ave_1 as
select document_handler, kind_of_document, r_creation_date format=datetime16., document_state
from unload.document
where kind_of_document="AVE"
and document_state in ("HOLD","TODO")
;
quit;
/* Sort on document_state */
proc sort in=ave_1 out=ave_1;
by document_state;
run;
/* Sort on document_handler. */
proc sort in=ave_1 out=ave_1;
by document_handler;
run;
/* Count all documents per state per handler and register oldest date per state per handler */
data ave_2 (keep=document_handler hold first_date1 todo first_date2);
set ave_1;
by document_handler;
retain hold first_date1 todo first_date2;
if first.document_handler then do;
hold=0;
first_date1=.;
todo=0;
first_date2=.;
end;
select (document_state);
when ('HOLD') do;
hold+1;
first_date1=ifn(first_date1=. or r_creation_date < first_date1,r_creation_date,first_date1);
end;
when ('TODO') do;
todo+1;
first_date2=ifn(first_date2=. or r_creation_date < first_date2,r_creation_date,first_date2);
end;
otherwise ignore+1;
end;
if last.document_handler then output;
keep document_handler hold first_date1 todo first_date2;
run;
