macro in proc sql

Accepted Solution Solved
Reply
Regular Contributor
Posts: 158
Accepted Solution

macro in proc sql

I'm trying to create macros in sql and I'm also trying to create a table and that my code and the error that I got 

 

proc sql ;
create table &visit.2 as
select count (distinct internal_visit_id) AS counttime, 1360 AS TIME_POINT_CONTRACTED
from &visit.1
where Earliest_Visit_Date ne .
select counttime , TIME_POINT_CONTRACTED into :counttimet, :timecont from &visit.2;
quit;

 

 

71 proc sql ;
72 create table &visit.2 as
73 select count (distinct internal_visit_id) AS counttime, 1360 AS TIME_POINT_CONTRACTED
74 from &visit.1
75 where Earliest_Visit_Date ne .
76 select counttime into :counttimet from &visit.2;
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: <, <=, <>, =, >, >=, EQ, EQT, EXCEPT, GE, GET, GT, GTT, INTERSECT, LE,
LET, LT, LTT, NE, NET, UNION, ^=, ~=.
 
ERROR 76-322: Syntax error, statement will be ignored.

Accepted Solutions
Solution
‎03-05-2018 04:08 PM
Super User
Posts: 23,667

Re: macro in proc sql

You’re missing the semicolon to end your first SELECT statement.

 

View solution in original post


All Replies
Solution
‎03-05-2018 04:08 PM
Super User
Posts: 23,667

Re: macro in proc sql

You’re missing the semicolon to end your first SELECT statement.

 

Super User
Posts: 13,502

Re: macro in proc sql

if the purpose of the second select statement is to restrict the data then it is 1) out of order and 2) likely needs parentheses:

 

Or you are missing a semicolon between two separate queries

proc sql ;
create table &visit.2 as 
select count (distinct internal_visit_id) AS counttime, 1360 AS TIME_POINT_CONTRACTED
from &visit.1
where Earliest_Visit_Date ne . ;

select counttime , TIME_POINT_CONTRACTED into :counttimet, :timecont from  &visit.2;    
quit; 
Super User
Super User
Posts: 8,070

Re: macro in proc sql

I find that you can prevent some of these types of mistakes by getting in the habit of formatting multi-line statements similar to how you would format a multiple line DO/END block. 

If 0=nmiss(x,y) then do;
   diff = x-y ;
   percent = diff/y ;
end;

That is place the termination code (the semi-colon) on the last line by itself.

proc sql ;
create table &visit.2 as 
  select count (distinct internal_visit_id) AS counttime
      , 1360 AS TIME_POINT_CONTRACTED
  from &visit.1
  where Earliest_Visit_Date ne .
;
select counttime , TIME_POINT_CONTRACTED into :counttimet, :timecont 
  from  &visit.2
;
quit; 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 128 views
  • 2 likes
  • 4 in conversation