Desktop productivity for business analysts and programmers

SAS to SQL

Reply
Contributor
Posts: 25

SAS to SQL

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

Super User
Posts: 19,006

Re: SAS to SQL

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

Super User
Posts: 19,006

Re: SAS to SQL

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?

Super User
Posts: 7,382

Re: SAS to SQL

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: SAS to SQL

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

Super User
Super User
Posts: 7,682

Re: SAS to SQL

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?

 

Super User
Posts: 7,382

Re: SAS to SQL


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,233

Re: SAS to SQL

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. 

Super User
Posts: 5,378

Re: SAS to SQL

@KurtBremser, the calculated key word is SAS specific.
Data never sleeps
Super User
Posts: 7,382

Re: SAS to SQL


LinusH wrote:
@KurtBremser, 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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 9 replies
  • 438 views
  • 4 likes
  • 6 in conversation