Trying to convert the following into one SQL statement, but can't wrap my head around the IF/THEN conditions. Is it possible to do in one SQL statement?
DATA TABLE1;
SET TABLE2;
IF OBS1 > 1 THEN
OBS3=((OBS4*.01)*(OBS5*215)) / ......;
ELSE
OBS3=((OBS4*.01)*(OBS5*315)) / ......;
IF OBS1 > 100 THEN OBS1 = 100;
RUN;
PROC SORT DATA=TABLE1;
BY OBS1, OBS2;
PROC SUMMARY DATA=TABLE1;
BY OBS1, OBS2;
VAR OBS3;
OUTPUT OUT=TABLE3;
MEAN(OBS3)=AVGOBS;
I know how to obviously get the PROC SORT and PROC SUMMARY accomplished with SQL. Just need help with the IF/THEN in SQL.
PROC SQL;
CREATE TABLE3 AS
SELECT OBS1, OBS2, AVG(OBS3) AS AVGOBS
GROUP BY OBS1, OBS2;
QUIT;
It's going to be easier to provide a solution if you give an example of the data you have and what result you are looking for.
Correct. But can I put a CASE statement in front of an AVG(observation) AS xxxx?
I want to calculate an average for one observation:
AVG(IF observation1 > 1 THEN observation2=observation2*2 ELSE observation2=observation2*3) AS new-observation;
I want observation2 calulated to new value before it's averaged.
If I understand your question correctly then yes. Put a comma in front and behind the case statement as if it were any other item you are selecting from the dataset.
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.