DATA Step, Macro, Functions and more

PROC SQL AVG with calculated observation

Reply
Contributor
Posts: 73

PROC SQL AVG with calculated observation

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;

Valued Guide
Posts: 858

Re: PROC SQL AVG with calculated observation

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.

Valued Guide
Posts: 858

Re: PROC SQL AVG with calculated observation

For if then look up pl/sql decode() or case statements.
Super User
Posts: 17,828

Re: PROC SQL AVG with calculated observation

IF/THEN convert most easily to CASE statements in SQL.
Contributor
Posts: 73

Re: PROC SQL AVG with calculated observation

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. 

Valued Guide
Posts: 858

Re: PROC SQL AVG with calculated observation

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.

Ask a Question
Discussion stats
  • 5 replies
  • 175 views
  • 0 likes
  • 3 in conversation