topic Re: PROC SQL AVG with calculated observation in SAS Programming

PROC SQL AVG with calculated observation

G_I_Jeff — Wed, 30 Sep 2015 18:11:20 GMT

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;

Re: PROC SQL AVG with calculated observation

Steelers_In_DC — Wed, 30 Sep 2015 18:42:31 GMT

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.

Re: PROC SQL AVG with calculated observation

Steelers_In_DC — Wed, 30 Sep 2015 18:43:48 GMT

For if then look up pl/sql decode() or case statements.

Re: PROC SQL AVG with calculated observation

Reeza — Wed, 30 Sep 2015 18:51:17 GMT

IF/THEN convert most easily to CASE statements in SQL.

Re: PROC SQL AVG with calculated observation

G_I_Jeff — Wed, 30 Sep 2015 18:56:16 GMT

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.

Re: PROC SQL AVG with calculated observation

Steelers_In_DC — Wed, 30 Sep 2015 19:14:24 GMT

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.