date converting problem

[nnl3256]

Pieces of code work before, but don't work now. The data test from SQL server and imported in csv format; test is subset of csv, containing 2 rows.

mif_data_reporting_dt is numeric in datetime 16 format. Attached test table and error message.  Thanks in advance!

proc import out=csv
datafile="F:\Shared\CMIP\Run81_sas_in.txt"
dbms=csv replace; 
getnames=Yes;
run;

data new_data;
    set test;
	rptqtr=qtr(mif_data_reporting_dt);
	rptyr=year(mif_data_reporting_dt);
run;

[Tom]

I don't understand. 

Why are you creating dataset CSV in the first step and then reading from dataset TEST in the second step?

If you have a CSV file then just write a data step to read it and you will have full control over how the variables are defined.

[Tom]

1,972,252,800 is not a valid date value.  With an average of 365.25 days per year that would be past the year 5 million.

734  data  test;
735    date=1972252800 ;
736    year = 1960 + int(date/365.25);
737
738    put date=comma32. year=comma32. ;
739    put date=datetime20.;
740  run;

date=1,972,252,800 year=5,401,693
date=01JUL2022:00:00:00

Probably your variable is actually a DATETIME value instead of a DATE value.  To find a QUARTER from a DATETIME value first convert the number of seconds to number of days.

qtr(datepart(datetime_value))

[nnl3256 · Accepted solution]

Thanks! it works if the date variable was treated ad in DATETIME value.

qtr(datepart(datetime_value))