topic Re: macro in proc sql in SAS Programming

macro in proc sql

mona4u — Mon, 05 Mar 2018 20:36:51 GMT

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;

______

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.

Re: macro in proc sql

Reeza — Mon, 05 Mar 2018 20:44:49 GMT

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

Re: macro in proc sql

ballardw — Mon, 05 Mar 2018 20:47:50 GMT

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;

Re: macro in proc sql

Tom — Tue, 06 Mar 2018 05:00:25 GMT

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;