What alternative function in sql

[thanikondharish]

Proc SQL;
Select dif(age) as difference from sashelp.class;
Quit;
When I run above program it gives error
Dif function can't be used in sql then what is other alternative
Please give me one more function

[Kurt_Bremser]

Comparisons with preceding observations are the domain of the data step. SQL isn't really good for this.

Do it in a data step.

[RW9]

Will you be marking this, or any post you have made here, answered by assigning Correct to one of the posts?

[Ksharp]

Post the data you have and output you want see .

[KachiM]

You want one more function to give what dif() does and you want ProcSQL to be used.

I am not aware of any other SAS function to replace dif().

However, SAS provided FUNCTION COMPILER to make your own function. Use PROC FCMP to create MYDIFF() function as follows:

proc fcmp outlib = work.cmpds.lib;
   function mydiff(Var);
      file log;
      static numtimes 1 prev 0;
      if numtimes = 1 then do; prev = Var; numtimes = 2; return(.); end;
      else do;
         d = Var - prev;
         prev = Var;
         return(d);
      end;
   endsub;
quit;

MYDIFF function is compiled and stored in a special place in your Computer(under WORK.CMPDS).  Use the function as:

options cmplib = work.cmpds;

Proc SQL;
   Select mydiff(age) as difference from sashelp.class;
Quit;

Cheers !

[RW9]

And then anyone using that code will be looking for a SAS function mydiff().  fcmp really is the worse thing that SAS have released in terms of obfuscation/coding hiding.  You cannot even tell that function is not one provided by SAS, I would highly recommend not going down that route.  

[KachiM]

Hi RW9,

No one can get FCMP function unless he looks for it in a special Library under special Data Set (here CMPDS) using the OPTIONS statement. Hence, I do not see any confusion in knowing  SAS Function from FCMP Function. If the OPTIONS statement is not present, the FCMP function will not be available for the Data Step or for other PROCS.

Regards,

DataSP

[RW9]

That catalog can be linked anywhere, at system level for instance.   If you see this code:

data want;
  set have;
  dolist(somevariable);
run;

Please point me to what dolist() function does, where the SAS guidance is for that function.  As a user I do not know that this is a system level function.  Obfuscation should be avoided totally, open source, plain simple open code available to everyone (and well documented).

In this instance, datastep does the task perfectly well, no need to use the additional subcomponent SQL, or write functions to support a subcomponent.  If there really is a need to do this in SQL, then assigning a flag for base, and compare and joining the two should be sufficient.

[KachiM]

Any FCMP function or Call Routine needs documentation. The user of the function, the SAS Programmer, has to document it as other SAS Functions. 

dolist(somevariable); 

If DOLIST() is a FCMP Function, it needs documentation - there is no doubt. It needs options statement to tell SAS where to find such a function. As long these are available, any special function which is not yet supported by SAS can be made using Proc FCMP.

This is my view.

[Reeza]

---

@RW9 wrote:

And then anyone using that code will be looking for a SAS function mydiff().  fcmp really is the worse thing that SAS have released in terms of obfuscation/coding hiding.  You cannot even tell that function is not one provided by SAS, I would highly recommend not going down that route.  

---

That's a different take. I think adding functionality to create customized functions is a great option. There's no way SAS can stay competitive without allowing user customization when basically every other tool allows it.

Regarding know if it's user defined or SAS function that should be identified in documentation if it's being used in production code.

I agree with defining functions in this particular case, because there are better alternatives, such as a self-join or data step. 

[RW9]

Well, its several levels really.  As someone who gets code from third party vendors, sometimes plain text, sometimes catalogs, I really do not appreciate binary proprietary files.

- First off is documentation, has anyone ever seen any for SAS code?  Every role I go into has a mass of creaking macros which were written in version 6, no lifecycle management or documentation.  Even trying to piece together how to use them is a lifetime of work.  That is with the ability to see the code underneath.

- Second off is the issue with catalogs.  As well highlighted by the 32bit to 64 move, catalogs are really bad.  They are not portable, and break with new versions.  With these being compiled, this means the code hidden within is then lost.  We have a lot of catalogs where the code is hidden and not available, now lost to time due to being 32bit, and the code based on it also no longer works.  Catalogs = bad.  In fact any proprietary binary file format is bad.

- In line with the above, if in code you see %xyz, you can at least see that it is a macro being called and highlights in a different color in editors.  If we then remove the % then the code becomes far less readable, and you can almost guarentee that someone will come up with subslr or something like that, which is, without close review, similar to substr.  Try debugging code like that.

- Finally there is there whole, is there a need for it, argument.  A large percentage of macro code I see is due to bad data structure (or storage modelling), or lack of understanding of SAS.  Macro really needs to be used only in a well documented specific process and follow lifecycle process.  I can guarantee that compiled macros will not have any lifecycle management until such time as they break, and then no-one will even know where they are.  This is especially true where many includes/setup files are run at startup linking various things. 

So what are the benefits:

Compiled functions run faster - well unless your running on vast data, its unlikely you will see any real benefit.

Compiled functions can be called anywhere, in SQL as well - SAS implements ANSI SQL, so code written in SAS can be copied to SQL and it will run, and ANSI compliant SQL vice versa from database.  Now put into that compiled functions, these are not portable, and hence you lose portability.

So from my side, the benefits are negligible, and the cons are so big it makes me grind my teeth.  

/Rant