CODEWITHSUNDEEP
sasproc-sql
PinkyL
PinkyL

Reputation: 361

issue with nested select statements in proc sql

I was given this code to run but I keep getting errors even after I've made sure there are the right number of left/right brackets. This is the original code as my adding of brackets seemed to be in the wrong place.

   proc sql;
    create table all as
    select distinct a.id, a.count, b.date
    from (select distinct id, count (*) as count from (select distinct id, p_id, date2 from table1) group by id) a
(select distinct id, min(date2) as date format datetime. from table1) b
where a.id=b.id;
quit;



(select distinct id, min(date2) as date format datetime. from
                  --------              -
                  22                    22
                  202                   76
3520! table1) group by id) b
ERROR 22-322: Syntax error, expecting one of the following: ), ','.

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

ERROR 76-322: Syntax error, statement will be ignored.

Edit: after adding a comma I then get this error:

256     , (select id, min(date2) as date format datetime. from
256! table1) group by id ) b
                                   -
                                   22
                                   76
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, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

257    Where a.id=b.id;
258  quit;

Upvotes: 0

Views: 1817

Answers (1)

sushil
sushil

Reputation: 1576

The error is not of brackets, but of comma (,) . You've missed comma sign at the start of 5th line.

, (select distinct id, min(date2) as date format datetime. from table1) b

EDIT: Indented the original code with my comma fix. I don't know why you are getting this new error. I copied your original code w/ comma addition and tested you code with dummy data and it's working fine. I guess some hidden junk character is causing error. 

data table1;
input id p_id date2 :yymmdd10.;
datalines;
1 1 2012-01-15
1 1 2012-01-15
2 1 2012-01-15
2 2 2012-01-15
4 1 2012-01-15
;;;;
run;
proc sql;
    create table all as
    select distinct a.id, a.count, b.date
      from (select distinct id, count (*) as count 
              from (select distinct id, p_id, date2 from table1) 
            group by id
            ) a
    , (select distinct id, min(date2) as date format datetime. 
         from table1
      ) b
where a.id=b.id;
quit;

Upvotes: 1

Related Questions