Can we use where and having clauses in a single SAS program.
ex: proc sql;
select a,b,c from test
where state in 'KA'
and having <some condition>.
Is the above program run correctly, if not why ?
Answers were Sorted based on User's Feedback
Answer / rauthu
Yes, WHERE and HAVING can be used in single program. HAVING must be after GROUP by clause.
Example:
proc sql;
select make, model, avg(msrp) as avgmsrp, msrp
from sashelp.cars
where make ='Honda'
group by model
having avgmsrp < 100000;
quit;
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / rajasekaran
having statement should mentioned after group by statement only
proc sql;
select a,b,c from test
where state in 'KA'
group by xyz
and having <some condition>.
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / a.k.naidu
Having clause can be used only with 'group by'. Difference between 'where' and 'having' is that former works on variable level and latter works on observation level. 'having' works like second where condition on "grouped data"
Is This Answer Correct ? | 2 Yes | 0 No |
Explain input and put function?
What is the function of output statement in a SAS Program?
How do you add a number to a macro variable?
How to select the observations randomly from a SAS dataset
How to create list output for cross-tabulations in proc freq?
Describe the function and untility of the most difficult SAS macro that you have written.
Suppose there is a SAS dataset with following values - Parent Child A B B C D E F G G H H I and so on….. This goes onto 1000s of observations/rows. Now how do we identify from this dataset Grandparents and Grandchildrens ?
5 Answers American Express, Barclays,
How would you code a merge that will write the matches of both to one data set, the non-matches from the left-most data set to a second data set, and the non-matches of the right-most data set to a third data set.
explain about sas business intelligence? : Sas-bi
Explain the difference between using drop = data set option in set and data statement?
How you are maintaining sas programmes in your company...any specific version control software you are using? If so, tell me the name?
What is the difference between the proc sql and data step?