BookmarkSubscribeRSS Feed
yudhishtirb
Calcite | Level 5

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

9 REPLIES 9
Reeza
Super User

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

Reeza
Super User

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?

yudhishtirb
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

@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
PROC Star

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
Tourmaline | Level 20
@Kurt_Bremser, the calculated key word is SAS specific.
Data never sleeps
Kurt_Bremser
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1539 views
  • 4 likes
  • 6 in conversation