topic Re: SAS to SQL conversion in SAS Programming

SAS to SQL conversion

jrajesh61 — Wed, 29 Nov 2017 09:38:24 GMT

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;

Re: SAS to SQL conversion

RM6 — Wed, 29 Nov 2017 09:40:07 GMT

hi can you post some sample data

Re: SAS to SQL conversion

jrajesh61 — Wed, 29 Nov 2017 09:42:37 GMT

Attached sample data here

Re: SAS to SQL conversion

jrajesh61 — Wed, 29 Nov 2017 09:48:23 GMT

Please note that in real dataset we have millions of records.

Re: SAS to SQL conversion

RW9 — Wed, 29 Nov 2017 09:54:09 GMT

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?

Re: SAS to SQL conversion

LinusH — Wed, 29 Nov 2017 09:54:30 GMT

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

Re: SAS to SQL conversion

jrajesh61 — Wed, 29 Nov 2017 10:07:24 GMT

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.

Re: SAS to SQL conversion

RW9 — Wed, 29 Nov 2017 10:17:33 GMT

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.

Re: SAS to SQL conversion

RM6 — Wed, 29 Nov 2017 10:39:58 GMT

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;

Re: SAS to SQL conversion

RW9 — Wed, 29 Nov 2017 11:14:44 GMT

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.

Re: SAS to SQL conversion

jrajesh61 — Wed, 29 Nov 2017 11:24:01 GMT

Hi,

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

Re: SAS to SQL conversion

RM6 — Wed, 29 Nov 2017 11:32:27 GMT

try using rank () over(partition by work_id) , instead of monotonic

Re: SAS to SQL conversion

LinusH — Wed, 29 Nov 2017 12:03:30 GMT

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

Re: SAS to SQL conversion

ballardw — Wed, 29 Nov 2017 15:59:12 GMT

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

Re: SAS to SQL conversion

ballardw — Wed, 29 Nov 2017 16:02:35 GMT

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

Re: SAS to SQL conversion

AhmedAl_Attar — Wed, 29 Nov 2017 17:00:20 GMT

Please be aware of the following:

- Microstrategy is a Reporting Platform that can connect to different underlying data sources (RDBMS, Hadoop, NoSQL Databaes, ...etc)

- "<function()> OVER ()" are SQL Analytical functions syntax supported by selective Databases and not necessary at the same level of support!

Having said, jrajesh61 will need to investigate the ability of his SQL based data source to support these analytical functions!

Just my 2 cents,

Ahmed