topic Unable to append Dates (YYMMDD10.) to sql table in stg schema in SAS Programming

Unable to append Dates (YYMMDD10.) to sql table in stg schema

Scooby3g — Tue, 14 May 2024 17:01:22 GMT

Hi all,

I am trying to use SAS to append a dataset into a SQL table.

I am using this code (modified the table names). My dates are in YYDDMM10. format, numeric = type, length = 8, informat =10. and they cannot be appended because of type of mismatch. The variables in the SQL table is set to the data type = "date" and it has allow nulls checked off.

any help is appreciated!! >.<

libname Dummy odbc dsn='Dummy_data' schema=stg;




data org.procedures;
set procedures;
RunDate = today();
format RunDate date9.;
if firstobs then Obs_ID = 1;
else Obs_ID +1;
run;


*remove exisiting data from table;
data Dummy.data_Numerators; 
modify Dummy.data_Numerators;
if Obs_ID not = '.0y' then
remove Dummy.data_Numerators;
run;

*append new data to table;
proc append data= org.procedures base = Dummy.data_Numerators force; run;

Re: Unable to append Dates (YYMMDD10.) to sql table in stg schema

ballardw — Tue, 14 May 2024 20:44:21 GMT

Datetime values in SAS are numbers of seconds. I would expect something with a datetime format to be such.

Date values in SAS are numbers of DAYs. So any combining of data sets or use needs to keep that in mind.

You can turn a Date value into a datetime with something like this:

dt_from_date = dhms(datevariable,0,0,0);

which assigns the hour, minute and second component of a datetime as 0 for the given date.

OR get the Date value from a datetime using:

datevalue = datepart( datetimevariable);

so you could append dates to dates or datetimes to datetimes.

HOWEVER, this statement : "My dates are in YYDDMM10. format, numeric = type, length = 8, informat =10" almost certainly means that your values are not actually dates either. I would guess that you have a numeric value that you imagine is a data such as 20240510 and think that is "10 May 2024". If you attempt to treat a number like 20240510 as a date you will find that is much larger than the largest date value SAS currently supports which is 6589336 or 31Dec20000 (yes year 20K).

So if you want a SAS date value of 10 May 2024 you need to either read from an external source with an informat like YYMMDD10 or convert your value:

data example;
   x=20240510;
   date = input(put(x,8.),yymmdd10.);
   format date yymmdd10.;
run;

Then use the DHMS function to create a datetime value.

Then append the data.

Caution: I have never used STG through ODBC links. I am not sure that the approach you have attempted with MODIFY will work. You may need to create a temporary SAS data set with similar code:

data work.data_Numerators; 
set Dummy.data_Numerators;
if Obs_ID not = '.0y' then delete;

run;

Append you data to this SAS data set. Then write it back to the Dummy library.

Re: Unable to append Dates (YYMMDD10.) to sql table in stg schema

Kurt_Bremser — Tue, 14 May 2024 21:10:18 GMT

Run PROC CONTENTS on both datasets, then take appropriate action so that the append dataset's variable attributes matches those of the base dataset.

Re: Unable to append Dates (YYMMDD10.) to sql table in stg schema

Patrick — Wed, 15 May 2024 01:34:46 GMT

What the warning tells you is that the data type on the SQL Server side must be Datetime or Datetime2 but that on the SAS side the variable uses a Date and not a Datetime format. The code you shared also shows that the variable on the SAS side stores a SAS Date (count of days since 1/1/1960) and not a SAS Datetime value (count of seconds since 1/1/1960).

This section LIBNAME Statement Data Conversions under the doc for the SQL Server access engine shows you how SAS and SQL Server column types map to each other.

If you populate and format your variable in your data step as a SAS datetime column then things will work.

data org.procedures;
  set procedures;
  RunDate = DATETIME();
  format RunDate DATETIME22.;
  if firstobs then
    Obs_ID = 1;
  else Obs_ID +1;
run;