0

I'm new to SQL and trying to run this piece of code to do a count on the frequency of each entry, but I'm getting an error (and can't figure out why - no real debugging in this environment, just "ERROR 22-322: Syntax error, expecting one of the following: ;, ',', USING. & ERROR 202-322: The option or parameter is not recognized and will be ignored.").

I'm compiling in SAS Enterprise Guide 6.1, by the way. Any help would be much appreciated! 

proc sql;
create view work.temp as 
select model_name, count(*) as frequency
from mytable_name
where model_type like '%Smartphones%'
and model_name not like '%Apple iPhone 5%'
group by model_name
order by count(*) desc 
limit 10;   
quit;
Joe
  • 62,789
  • 6
  • 49
  • 67
cappuccino
  • 3
  • 1
  • 2
  • 1
    Are you using `proc sql` for this because you want to learn it, or for some other reason? – Joe Jul 03 '15 at 19:24
  • I'm just starting out and everyone uses it around here - they all just compile SQL code in SAS. I'm trying to learn it as I go. – cappuccino Jul 03 '15 at 19:28
  • Quick thing you'll learn: every SQL compiler is different. Oracle, SQL Server, mySQL, SAS, etc., all have different implementations that are very basically the same but have significant differences around the edges. – Joe Jul 03 '15 at 19:36
  • Also - your SAS version (probably 9.4 if you have EG 6.1) matters more than your Enterprise Guide version. EG is just your IDE, it doesn't have anything to do with what's on the backend doing the actual work. – Joe Jul 03 '15 at 19:36
  • Finally - if you're using SAS, at least learn a bit of regular SAS. A lot of things like you're doing above are going to be easier and faster using the built-in procedures. `PROC FREQ` and `PROC MEANS` are almost guaranteed to be faster than `PROC SQL` for this kind of thing, because they're optimized for what they're doing. – Joe Jul 03 '15 at 19:38

2 Answers2

1

limit 10 is not valid code in SAS proc sql. In general, there isn't a good way to limit observations on output in SAS PROC SQL; (obs=10) is not valid as a dataset option for some reason.

You also can't order by count(*); you can only order by the created column.

So you could do this:

proc sql;
  create view work.temp as 
    select model, count(*) as frequency
      from sashelp.cars
      group by model
      order by 2  desc 
;
quit;

2 there refers to the second column (so, the frequency column).

If you want to limit to a smaller number of observations, you can use (obs=#) where you actually use the view, in some cases (but not in others - in general, I don't think it's valid in PROC SQL in most places).

In general this particular query is probably easier in base SAS rather than SQL, especially if you want to limit its output.

proc freq data=sashelp.cars order=freq noprint;
  where origin='Asia';
  tables make/out=makelist(where=(count>10) keep=make count);
run;

That will run a bit faster than SQL (assuming you don't gain anything from the view, anwyay; if you're only outputting a few rows view won't help you much). Of course here I'm limiting to a particular count not a particular # of obs, but it's much the same.

To accomplish the title's question, you could then simply select the top 10 entries from that dataset (assuming you asked PROC FREQ to return the dataset sorted by frequency as I do above):

data topten;
  set makelist;
  if _n_ le 10;  *first 10 iterations;
run;

Or use proc rank or any number of options.

Joe
  • 62,789
  • 6
  • 49
  • 67
1

You can use the OUTOBS= option of PROC SQL to limit observations.

proc sql outobs= 10;
    create view work.temp as 
    select model_name, count(*) as frequency
    from mytable_name
    where model_type like '%Smartphones%'
    and model_name not like '%Apple iPhone 5%'
    group by model_name
    order by count(*) desc;   
quit;

If you are trying to learn SQL in SAS using some sample code then I would recommend going through these examples from SAS.

Pasha
  • 11
  • 2