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;
Attached sample data here
Why, isn't the current program working...?
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?
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.
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.
@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".
So have the decency to hire skilled personnel, instead of trying to get community do your work for free.
@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?
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.
Hi,
Your correct Monotonic is not working here in SQL. Is there any alternative for this ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.