topic Re: SAS to SQL in SAS Enterprise Guide

SAS to SQL

yudhishtirb — Mon, 03 Oct 2016 12:48:42 GMT

Hello Team,

I am trying to conver below statement is SQL.

SAS :

if dtfileclosed ne . then dtQueryResolvedDate=dtfileclosed;
if dtQueryResolvedDate = . then dtQueryResolvedDate="01JAN1900:00:00:00.000"dt;

SQL :

CASE
WHEN #Master_2.dtFileClosed IS NOT NULL THEN #Master_2.dtFileClosed
WHEN dtQueryResolvedDate IS NULL THEN '01-01-1900'
ELSE dtQueryResolvedDate
END AS dtQueryResolvedDate

SAS :

if dtQueryResolvedDate = "01JAN1900:00:00:00.000"dt then resolved = 0; else resolved=1;

SQL :

CASE WHEN #master_query_4.dtQueryResolvedDate = '01-01-1900' THEN 0 ELSE 1 END AS resolved

But it is not showing correct output ?

Can someone please help me ?

Thanks In Advance

Re: SAS to SQL

Reeza — Mon, 03 Oct 2016 13:05:03 GMT

SAS date literal are specified as ddMONyyyy ie '01Jan1990'd not '01-01-1900'

Re: SAS to SQL

Reeza — Mon, 03 Oct 2016 13:06:53 GMT

Actually, it's date time not a date so use the original value specified. Not sure why you changed it.

Are you switiching to SAS SQL or a different type?

Re: SAS to SQL

Kurt_Bremser — Mon, 03 Oct 2016 13:21:06 GMT

I guess the OP is switching to a non-SAS SQL, as the table names are not SAS-compliant (start with a #).

Re: SAS to SQL

yudhishtirb — Mon, 03 Oct 2016 13:30:54 GMT

Hi,

# means temperory tables just like datasets we are creating in SAS.

Also it dont have any impact of changing date to datetime.

I just need explanation for below line

if dtfileclosed ne . then dtQueryResolvedDate=dtfileclosed

What actually this line does ?

Please let me know

Thanks

Re: SAS to SQL

RW9 — Mon, 03 Oct 2016 13:39:20 GMT

if dtfileclosed ne . then dtQueryResolvedDate=dtfileclosed

Assuming dtfileclose is a SAS date variable, then it is a numeric value - number of days since a certain timepoint. If it contains no data, then as per numerics it is ., which is the missing numeric data in SAS. So the statement is saying, if dtfileclosed has some data, then set the variable dtqueryresolveddate to be the value in dtfileclosed.

Case equivalent:

case when dtfileclosed is not null then dtfileclosed

else . end as dtqueryresolveddate

What is happening, as previous posters have mentioned is that you are mixing up SQL datatypes and SAS datatypes. Why do you need SQL for this? Post exact full code, showing which system you are using, is this pass through, is it within SAS, is it nothing to do with SAS at all?

Re: SAS to SQL

Kurt_Bremser — Mon, 03 Oct 2016 13:45:05 GMT

@yudhishtirb wrote:

Hi,

# means temperory tables just like datasets we are creating in SAS.

Also it dont have any impact of changing date to datetime.

I just need explanation for below line

if dtfileclosed ne . then dtQueryResolvedDate=dtfileclosed

What actually this line does ?

Please let me know

Thanks

If dtfileclosed is not missing (. is a missing numerical value in SAS) then set dtQueryResolvedDate to the value of dtfileclosed

I managed to get your data step results with the following code in SQL also:

data have;
input dtfileclosed :datetime23.3 dtqueryresolveddate :datetime23.3;
format dtfileclosed datetime23.3 dtqueryresolveddate datetime23.3;
cards;
. .
03oct2016:00:00:00.000 .
. 03oct2016:00:00:00.000
02oct2016:00:00:00.000 03oct2016:00:00:00.000
;
run;

data want1;
set have;
if dtfileclosed ne . then dtQueryResolvedDate=dtfileclosed;
if dtQueryResolvedDate = . then dtQueryResolvedDate="01JAN1900:00:00:00.000"dt;
if dtQueryResolvedDate = "01JAN1900:00:00:00.000"dt then resolved = 0; else resolved=1;
run;

proc print noobs;
run;

proc sql;
create table want2 as
select
  case
    when dtfileclosed ne . then dtfileclosed
    when dtQueryResolvedDate = . and dtfileclosed = . then "01JAN1900:00:00:00.000"dt
    else dtQueryResolvedDate
  end as dtQueryResolvedDate format=datetime23.3,
  case
    when calculated dtQueryResolvedDate ne "01JAN1900:00:00:00.000"dt then 1
    else 0
  end as resolved
from have;
quit;

proc print noobs;
run;

The result is:

          dtfileclosed        dtqueryresolveddate    resolved

                     .     01JAN1900:00:00:00.000        0   
03OCT2016:00:00:00.000     03OCT2016:00:00:00.000        1   
                     .     03OCT2016:00:00:00.000        1   
02OCT2016:00:00:00.000     02OCT2016:00:00:00.000        1   
                                                             

                dtQueryResolvedDate    resolved

             01JAN1900:00:00:00.000        0   
             03OCT2016:00:00:00.000        1   
             03OCT2016:00:00:00.000        1   
             02OCT2016:00:00:00.000        1

Note that if you want to base a CASE statement on the result of another CASE, use "calculated" to distinguish the newly created value from the original database column.

Re: SAS to SQL

LinusH — Mon, 03 Oct 2016 15:55:06 GMT

@Kurt_Bremser, the calculated key word is SAS specific.

Re: SAS to SQL

SASKiwi — Mon, 03 Oct 2016 19:00:33 GMT

Your query appears to be using SQL Server Transact-SQL. There is no equivalent in SQL Server to SAS's missing values although IS NULL or IS NOT NULL may work.

Re: SAS to SQL

Kurt_Bremser — Tue, 04 Oct 2016 06:33:33 GMT

@LinusH wrote:
@Kurt_Bremser, the calculated key word is SAS specific.

Well, after all, this is the SAS Communities website, so one should expect answers that work in SAS. If one has to deal with other software, there are surely forums for that.

And if someone is tasked with migrating processes to a certain system, they should have sufficient knowledge for that system in the first place.