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=p119l8ejs... */
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;
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!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.