topic Re: SAS to SQL in SAS Programming

SAS to SQL

VinayakKankate — Wed, 23 Nov 2016 14:18:38 GMT

Please help in the following SAS command to get it translated into SQL

if datepart(abc) <= datepart(xyz) <= datepart(abc)+7

Thanks

Re: SAS to SQL

Kurt_Bremser — Wed, 23 Nov 2016 14:25:15 GMT

The datepart function can also be used in PROC SQL.

if datepart(abc) <= datepart(xyz) <= datepart(abc)+7;

appears as a standalone statement (without a then ....) in a data step, it is called a subsetting if, and can be done in SQL in a where condition. If abc or xyz are variables that are newly created in the SQL, use the "calculated" keyword.

Re: SAS to SQL

VinayakKankate — Wed, 23 Nov 2016 14:28:26 GMT

Hey KurtBremser,

Thanks for your instant reply,

we need this to be converted into SQL server.

Thanks

Re: SAS to SQL

Cynthia_sas — Wed, 23 Nov 2016 14:37:01 GMT

You can also use BETWEEN with a WHERE. Does your SQL server support BETWEEN?
where datepart(xyz) between datepart(abc) and datepart(abc) + 7;

The DATEPART function is a SAS-specific function. So it will work in SAS Proc SQL. I do not know whether it will work in SQL server.

cynthia

Re: SAS to SQL

Kurt_Bremser — Wed, 23 Nov 2016 14:48:58 GMT

@VinayakKankate wrote:

Hey KurtBremser,

Thanks for your instant reply,

we need this to be converted into SQL server.

Thanks

Then you will have to look for functional equivalents of the datepart() function in the MS SQL Server documentation.