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.
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.