topic Re: Case Statement equivalents in CASL in Developers

Case Statement equivalents in CASL

SundareshS — Fri, 16 Feb 2018 14:10:17 GMT

Hi all,

I am trying to rewrite various Proc SQL like queries in CAS and have relied on the simple action set for most of them. Perhaps I am missing this in the documentation / examples, but can anyone help me understand how to write a CASE WHEN condition ?

An example would be the code below - I count the number of distinct players per team in the BASEBALL dataset. But what if I need to count only those players who have nruns > X ?

thanks

Sundar

/* http://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.11&docsetId=casanpg&docsetTarget=p119l8ejshp7gjn157ttba5rpmuc.htm&locale=en */





proc cas;

   session mysession;

   simple.distinct /  

      inputs={"Name"}

      table={

      name="baseball",

      groupBy={"TEAM"}

      }

      casout={

         name="bb_summary",

         replace=True

      };

      ;

      table.save /                     /*3*/

      table={name="bb_summary"}

         name="bb_summary.sashdat"

         replace=True

      ;

run;

quit;

Re: Case Statement equivalents in CASL

GinaRepole — Tue, 20 Feb 2018 15:22:04 GMT

If you want something even more like PROC SQL, FedSQL is CAS compatible and can be accessed via PROC FEDSQL.

But since we're going with action sets, I believe that the intent of developers was to follow through with SAS norms and not SQL norms. The SAS syntax norm is use of the IF/THEN statement, which performs the same functions as a CASE/WHEN in SQL. You should be able to see some use of it in this example.

Some not-real and not-working code here to simplify the examples in the above documentation:

PROC CAS;
     IF (nvar > X) THEN RETURN(X);
RUN;

I am not well-versed in CAS action sets yet, but hopefully this helps point you in the right direction!

Re: Case Statement equivalents in CASL

SundareshS — Fri, 23 Feb 2018 15:33:51 GMT

Thank you! I did use Fed SQL to carry out the summarization (and we lose nothing from proc sql there) ; I was trying to see if the options provided by CASL fulfill almost everything we do in SQL.

For example - in the SAS Viya Data Studio custom code window, one can either code in data steps or CASL. Not ideal, (irritating, rather :)) , but just want to make sure we are covered in such situations.

The use of the IF , along with the simple.summary actionsets does do most of what a case when statement does. I just want to be extra sure about a count distinct + CASE WHEN situation though (count(distinct case when...) - let me attempt something and get back

Thanks again.