topic Re: date converting problem in SAS Programming

date converting problem

nnl3256 — Fri, 07 Jul 2023 21:47:33 GMT

Re: date converting problem

nnl3256 — Fri, 07 Jul 2023 22:01:43 GMT

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;

Re: date converting problem

Tom — Fri, 07 Jul 2023 22:06:43 GMT

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.

Re: date converting problem

Tom — Fri, 07 Jul 2023 22:14:17 GMT

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))

Re: date converting problem

nnl3256 — Mon, 10 Jul 2023 17:56:23 GMT

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

qtr(datepart(datetime_value))