SAS日期格式与SQL Server日期不兼容 [英] SAS Date Formats Incompatible with SQL Server Date

问题描述

我对SAS还是很陌生，最近我们将一些SAS数据集迁移到了SQL Server表中，但是我们仍在使用SAS进行分析.当SAS试图从SQL Server表中引入数据并让SAS检查srv_edt日期是否在dos_beg_dt1和dos_end_dt1的SAS日期之间时，我遇到了一个问题.

I'm fairly new to SAS and recently we migrated some of our SAS datasets to a SQL Server table but we are still using SAS to do our analysis. I have run into a problem when SAS is trying to bring in the data from the SQL Server table and have SAS check if the srv_edt date is between the SAS dates of dos_beg_dt1 and dos_end_dt1.

当SAS尝试比较日期时，出现以下错误:错误:WHERE子句运算符需要兼容的变量.

When SAS tries to compare the dates I get an error of: ERROR: WHERE clause operator requires compatible variables.

dos_beg_dt1，dos_end_dt1和srv_edt(SQL日期格式)都以yyyy-mm-dd的格式出现".当我将srv_edt带入SAS表时，它会将其读取为字符日期.因此，我尝试更改日期的格式，然后会收到类似以下错误:错误:变量srv_edt已定义为字符和数字.我似乎找不到正确的格式或函数来让SAS进行比较，以查看srv_edt(SQL)是否在dos_beg_dt1和dos_end_dt1 SAS日期之间.

The dos_beg_dt1, dos_end_dt1, and srv_edt (SQL date format) all "appear" in the format of yyyy-mm-dd. When I bring the srv_edt into a SAS table it reads it as a character date. So I've tried changing the format of the dates and then I will get an error like:ERROR: Variable srv_edt has been defined as both character and numeric. I can't seem to find the correct format or function to get SAS to do the comparison to see if the srv_edt (SQL) is between the dos_beg_dt1 and dos_end_dt1 SAS dates.

我使用的代码如下:

libname sql odbc dsn=test schema=dbo;

%let dos_beg_dt1 = %sysfunc(intnx(qtr,&date,-1,beginning),yymmdd10.);
%let dos_end_dt1 = %sysfunc(intnx(qtr,&date,-1,end),yymmdd10.);

data sample;
set sql.table;
where &dos_beg_dt1 <= srv_edt <= &dos_end_dt1;
run;

作为参考，我正在使用SAS 9.2通过odbc连接到SQL Server 2008.

For reference I am using SAS 9.2 to connect via odbc to SQL Server 2008.

任何帮助或建议，将不胜感激.

Any help or advice would be greatly appreciated.

推荐答案

SAS存储并使用dat作为数字变量.如果您在将数据库迁移到SQL Server时没有将srv_edt列标识为日期列，那么现在一切都将正确处理.

SAS stores and uses dats as numeric variables. If you had not identified the column srv_edt as a date column when you migrated the database to SQL server everything would now process correctly.

我假设当前和将来您将表存储在SQL Server中，所有处理都将在SAS中进行.

I assume that currently and into the future you will just store the tables in SQL server and all the processing will be in SAS.

您有一些选择.

1/重新迁移SAS表，但将所有日期，时间和日期时间列标识为纯数字.它们都可以存储为8字节浮点数.日期变量也可能以长整数存储(在SQL Server中).代码需要稍作更改，以便宏变量为数字.

1/ re-migrate the SAS tables but identify all the date, time and datetime columns as just numeric. They all can be stored as 8 byte floating point. The date variables may also be stored (in SQL Server) is long integers. The code would need a slight change so that the macro variables would be numeric.

%let dos_beg_dt1 = %sysfunc(intnx(qtr,&date,-1,beginning));
%let dos_end_dt1 = %sysfunc(intnx(qtr,&date,-1,end));

2/使用SQL Server格式保留日期，时间和日期时间变量，并在使用数据时更改列的数据类型. (请注意，在输出上将需要相反的操作). SQL Server将日期变量显示为字符串(字符)，因此您的上面的表达式需要为-

2/ keep the date, time, and datetime variables in SQL Server format and change the data type of the column when using the data. (Note the reverse will be necessary on output). SQL Server will present the date variables as strings (character) so that your expression above will need to be -

%let dos_beg_dt1 = %sysfunc(intnx(qtr,&date,-1,beginning));
%let dos_end_dt1 = %sysfunc(intnx(qtr,&date,-1,end));

data sample;
set sql.table;

where &dos_beg_dt1 <= (input(srv_edt, yymmdd10.0))  <= &dos_end_dt1;

要确保在使用SAS处理时类型是数字，这就是输入函数将要执行的操作.

That to make sure when using SAS processing the type is numeric which is what the input function will do.

3/将日期，时间和日期时间变量保持为SQL Server格式，并更改工作方式以适应这一事实.也就是说，比较将使用字符数据，而输出将需要产生字符. SQL Server将日期变量显示为字符串(字符)，因此您的上面的表达式需要为-

3/ keep the date, time, and datetime variables in SQL Server format and change your working to accommodate that fact. That is comparisions will be using character data and output will need to produce characters. SQL Server will present the date variables as strings (character) so that your expression above will need to be -

%let dos_beg_dt1 = %sysfunc(intnx(qtr,&date,-1,beginning), yymmdd10.);
%let dos_end_dt1 = %sysfunc(intnx(qtr,&date,-1,end), yymmdd10.);

data sample;
set sql.table;

where ("&dos_beg_dt1" <= srv_edt) and
   (srv_edt <= "&dos_end_dt1");`

请注意，双引号"必须在宏变量周围 比较是数字的.

Note double quotes " " required surrounding macro variables as this comparison is numeric.

这篇关于SAS日期格式与SQL Server日期不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！