SAS to SQL

[yudhishtirb]

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

[Reeza]

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

[Reeza]

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?

[Kurt_Bremser]

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

[yudhishtirb]

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

[RW9]

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?

[Kurt_Bremser]

---

@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.

[SASKiwi]

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. 

[LinusH]

@Kurt_Bremser, the calculated key word is SAS specific.

[Kurt_Bremser]

---

@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.