BookmarkSubscribeRSS Feed
jrajesh61
Calcite | Level 5

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;

15 REPLIES 15
RM6
Obsidian | Level 7 RM6
Obsidian | Level 7
hi can you post some sample data
jrajesh61
Calcite | Level 5

Attached sample data here

jrajesh61
Calcite | Level 5
Please note that in real dataset we have millions of records.
LinusH
Tourmaline | Level 20

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

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

jrajesh61
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
ballardw
Super User

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

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

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.

jrajesh61
Calcite | Level 5

Hi,

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

RM6
Obsidian | Level 7 RM6
Obsidian | Level 7
try using rank () over(partition by work_id) , instead of monotonic

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 1603 views
  • 2 likes
  • 6 in conversation