SAS to SQL conversion

Reply
Occasional Contributor
Posts: 5

SAS to SQL conversion

Hi,

I'm pretty  new SAS. I have a piece of SAS code (pasted below) which has to be re-written in SQL. Text in bold represents the columns from the Dataset .Please let me know if you need more details.

 

data egs_work_suspends_2;
 set egs_work_suspends_1;
 by work_id;

 format saveprevstatus $100.;
 retain saveprevstatus;
 format savetmstp datetime26.6;
 retain savetmstp;

 prevstatus = saveprevstatus;
 format suspendtmstp datetime26.6;
 suspendtmstp = savetmstp;
 format suspendtime 8.;

 if work_status = 'Suspended' then do;
  saveprevstatus = work_status;
  savetmstp = work_status_tmstp;
 end;
 if prevstatus = 'Suspended' and work_status = 'Resumed' then do;
  paired = 1;
  suspendtime = work_status_tmstp - suspendtmstp;
  saveprevstatus = .;
  savetmstp = .;
 end;

Contributor RM6
Contributor
Posts: 24

Re: SAS to SQL conversion

Posted in reply to jrajesh61
hi can you post some sample data
Occasional Contributor
Posts: 5

Re: SAS to SQL conversion

Attached sample data here

Occasional Contributor
Posts: 5

Re: SAS to SQL conversion

Posted in reply to jrajesh61
Please note that in real dataset we have millions of records.
Super User
Posts: 5,490

Re: SAS to SQL conversion

Posted in reply to jrajesh61

Why, isn't the current program working...?

Data never sleeps
Super User
Super User
Posts: 8,174

Re: SAS to SQL conversion

Posted in reply to jrajesh61

What do you mean "it has to be converted into SQL"?  There is only one reason why you would need SQL, and that is if you were passing the code through to a database, or coding directly on a database.  If that is the case, then you need to know what the database is, what functionality it offers etc.  The only real code here is the if statements, these can be converted to case statements.  The real problem here lies in the retain.  SQL does not have a concept of row observation like you do in SAS, so you don't retain values over.  You need to identify a specific record based on some logic, and then merge that on.  Now without any data its hard to say, but as you have some sort of sequence I assume you have a date/time sequence, so the previous state is logical defined as having max(datetime < current datetime), assuming your data is unique this should return the most recent record to the obs you are dealing with.  As a breif totally in the dark example:

proc sql;
  create table WANT as 
  select  A.*,
          case when exists(select WORK_STATUS from HAVE group by ... having max(DATETIME) < A.DATETIME and WORK_STATUS="Suspended") then 1 else 0 as PAIRED,
   ...
  from    HAVE;
quit;

As you can see the coding is a real pain.  If you have working SAS code, why bother going through the whole process of development testing and such like for no purpose?

Occasional Contributor
Posts: 5

Re: SAS to SQL conversion

Thanks for your response. Here my requirement is to convert Reports in SAS to Microstrategy. To do that I need to write down the freeform SQL by looking into SAS code. I'm stuck in this piece of code in SAS and didn't find any option to Mimic the same in SQL .That's the  reason I reached out this forum for help in converting given piece of SAS to SQL and which would allow me to proceed further.

Super User
Super User
Posts: 8,174

Re: SAS to SQL conversion

Posted in reply to jrajesh61

Well, my previous post should help, though I have never heard of Microstrategy.  The two things to look at are:

retain savetmstp;

- This is not in SQL (unless your app provides a function for it), so you need to code a logical sub query or join for it.

 

if (xyz) 

- These are done as case statements in SQL.

 

Other than that not really much I can say.

Super User
Posts: 11,558

Re: SAS to SQL conversion


RW9 wrote:

Well, my previous post should help, though I have never heard of Microstrategy.  The two things to look at are:

retain savetmstp;

 


Apparently Microstrategy is another product similar to Tableau doing "enterprise reporting".

Super User
Posts: 5,490

Re: SAS to SQL conversion

Posted in reply to jrajesh61

So have the decency to hire skilled personnel, instead of trying to get community do your work for free.

Data never sleeps
Super User
Posts: 11,558

Re: SAS to SQL conversion

Posted in reply to jrajesh61

jrajesh61 wrote:

Thanks for your response. Here my requirement is to convert Reports in SAS to Microstrategy. To do that I need to write down the freeform SQL by looking into SAS code. I'm stuck in this piece of code in SAS and didn't find any option to Mimic the same in SQL .That's the  reason I reached out this forum for help in converting given piece of SAS to SQL and which would allow me to proceed further.


If your other software doesn't have the reporting tools you need then ...

There is a great deal of SAS data step code that will not translate into SQL due to the nature of the differences in designed functionality. Which is one reason SAS has Proc SQL to use the features of SQL that are impossible or extremely cumbersome to implement in a data step.

 

And what will you do with SAS analytic procedure code?

Contributor RM6
Contributor
Posts: 24

Re: SAS to SQL conversion

Posted in reply to jrajesh61
this might be close try it

proc sql;
create table x_1 as
select a.*,b.work_status as saveprevstatus,b.work_status_tmstp as savetmstp,
case when b.work_status = 'Suspended' and a.work_status = 'Resumed' then a.work_status_tmstp - b.work_status_tmstp end as suspendtime,
case when b.work_status = 'Suspended' and a.work_status = 'Resumed' then 1 end as paried
from (select *,MONOTONIC() as rnk from egs_work_suspends_1 ) a
left join (select *,MONOTONIC() as rnk from egs_work_suspends_1 ) b
on a.work_id=b.work_id
and a.rnk=b.rnk+1
;
quit;
Super User
Super User
Posts: 8,174

Re: SAS to SQL conversion

I am afraid not @RM6, monotonic() is a SAS function.  The OP is talking about coding SQL in another application which will a) implement ANSI SQL - this is standard SQL across all databases and what SAS also implements, and then b) the applications own function layer on that, so for example SAS provides it own functions on top of SQL, Oracle has its own library of functions in addition to ANSI SQL etc.  So functions used in one will likely not work in another unless there is a similar function built in that software.

Occasional Contributor
Posts: 5

Re: SAS to SQL conversion

Hi,

Your correct Monotonic is not working here in SQL. Is there any alternative for  this ?

Contributor RM6
Contributor
Posts: 24

Re: SAS to SQL conversion

Posted in reply to jrajesh61
try using rank () over(partition by work_id) , instead of monotonic
Ask a Question
Discussion stats
  • 15 replies
  • 167 views
  • 2 likes
  • 6 in conversation