PROC SQL: Why wont this code work

[BobHope]

Hi,

This is a bit of a nitpicking, but to me it is really important to get codes work without warnings and as few datasets as possible, so this is why I ask this.

I am frustrated cause my code wont work. It will work if I leave the most outer select statement out. Probably this is just too easy to figure out but Ive struggled with it and haven't come up with a solution.

proc sql noprint;

create table chart as

select *,monotonic() as id

from (

select distinct var2,count(var2) as count

from (

select distinct var1,var2

from report)

group by var2

order by count)

where id<5;

quit;

run;

The error I get is following:

268        var2 from report) group by var2  order by count);
                                                        _____
                                                        22
                                                        202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /,
              <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GT, GTT,
              HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, OUTER,
              UNION, ^, ^=, |, ||, ~, ~=. 

ERROR 202-322: The option or parameter is not recognized and will be ignored.

[arnouxvr]

I would just like to see the structure of your dataset.

Please run one of the following sets of code:

proc sql;

  describe table report;

quit;

or

proc contents data=report;

run;

and share the structure of the dataset.

[BobHope]

create table WORK.REPORT( bufsize=16384 )

  (

   var1 char(25),

   var2 char(160) format=$160. informat=$160.,

   var3 char(160) format=$160. informat=$160.,

   var4 char(160) format=$160. informat=$160.,

   var5 char(40) format=$40. informat=$40.,

   var6 char(8) format=$8.,

   var7 char(4) format=$4.,

  );

[arnouxvr]

Try the following:

proc sql noprint;

create table chart as

select *,monotonic() as id

from (

select distinct var2,count(var2) as count

from (

select distinct var1,var2

from report)

group by var2)

where id<5

order by count;

quit;

I moved the order by count right after the where statement.

Just make sure the where selection will work with your ID column.

[Scott_Mitchell]

My apologies for providing the same solution as Arnoux.  His reply didn't appear until after I posted.

[BobHope]

It didn't work, says var id not found. Somehow it seems thats SAS tries to do all of this

group by var2)

where id<5

order by count;

to the subquery part. Dunno why. I also forgot to mention that it is crucial to get the sorting happen before the monotonic() since this is just one block of my code and I use monotonic() to filter out the 5 most frequent occurances of var2.

[Scott_Mitchell]

Try my solution.  I believe you need to use Calculated in your where clause when referring to ID because it is created in the select statement .

[Vince28_Statcan]

I don't think it can be done within a single create table statement. Maybe with SAS 9.4 in a proc DS2 using a SQL select syntax for your set statement. In the mean time, you need your monotonic() function to be applied after your data is ordered but proc sql limits the order by statement to the outer query and proceeds it after everything else effectively incompatible to the desired result.

If you randomly happen to have an oracle server, there is apparently an automated ROWNUM variable similar to that of _N_ which might enable you to achieve the results, or not. This was just stuff I read earlier today trying to find ways around the limitation of order by as the final step of the outer query.

Fugue, your code will generate an error. In order for the monotonic() as id variable to be created, the monotonic() function has to be called somewhere else. You would minimally need to do where calculated id<5 or where monotonic()<5. It is probably one of the reasons why it is an undocumented function, it behaves oddly. Sadly, it wouldn't achieve the OPs' desired result since he needs the data to be sorted by count desc before the where statement applies as discussed in a reply.

[Patrick]

If I understand right what you're trying to achieve then may be a combination of SQL and data step is easier to code and also avoids the usage of monotonic() which is an unsupported function with some known issues.

proc sql noprint;
  create view V_chart as
    select *,count(distinct var1) as count
    from report
    group by var2
    order by count desc
    ;
quit;

data chart(drop=_:);
  set V_chart;
  by descending count;
  if first.count then _rank+1;
  if _rank<=5 then output;
  else stop;
run;

[Scott_Mitchell]

Is this what you are looking to do?

PROC SQL NOPRINT;

CREATE TABLE CHART AS

SELECT *,MONOTONIC() AS ID

FROM (

SELECT DISTINCT VAR2,COUNT(VAR2) AS COUNT

FROM (

SELECT DISTINCT VAR1,VAR2

FROM REPORT)

GROUP BY VAR2

)

WHERE CALCULATED ID<5

ORDER BY COUNT;

QUIT;

RUN;

[Scott_Mitchell]

What are you actually trying to do?

i understand what the code is doing, I just need to know if it doing what you want it to do.

[Fugue]

proc sql;

create table chart as

select *

from

( select monotonic() as id, var2, count ( *) as count

from

( select distinct var1, var2

from report )

group by var2 )

where id < 5

;

quit;

[DBailey]

haven't tried it..but it might be getting confused with the variable names.  Have you tried using aliases?

proc sql noprint;

create table chart as

select t1.*,monotonic() as id

from (

select distinct t2.var2,count(t2.var2) as count

from (

select distinct t3.var1, t3.var2

from report as t3) as t2

group by t2.var2

order by count) as t1

where id<5;

quit;