issue with nested select statements in proc sql

Question

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;

I thnk @sushil is right, there is a missing comma but I also think you need to include an equals sign in your format statement "format=datetime." — Jay Corbett, Apr 02 '15 at 16:51

sushil · Accepted Answer · 2015-04-02T18:02:34.847

1

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;

edited Apr 02 '15 at 18:02

answered Apr 02 '15 at 16:27

sushil

1,576
10
14

I count 2 left brackets and 3 right brackets, shouldn't it always be equal? I am not sure but since I added the comma, it is still giving me error. – PinkyL Apr 02 '15 at 17:44
@PinkyL please check the edit made to the answer. I had indented your original code and added comma fix and tested your code w/ dummy data and it's working fine. – sushil Apr 02 '15 at 18:04
I see what the issue was now, there's an extra group by id in my original that was probably causing all the problems. I took it out and formatted it to your example, it's working...thanks so much. – PinkyL Apr 02 '15 at 18:12

issue with nested select statements in proc sql

1 Answers1