topic multimacrovariables in sql in SAS Procedures

multimacrovariables in sql

michtka — Thu, 07 Jun 2012 16:59:08 GMT

Hy everyone, I want to assign two macrovariables via sql,

I got the next dataset and code below, but the macrovariable &n1 and &n2 cant be resolved, appearing a warning message in my window log :

WARNING: INTO clause that is not in the outermost query block will be ignored.

the idea is assigned to the macrovariable &n1 the value of the treatment 1, n=3, and &n2 get the value of the treatment 2, n=2.

I try in my code the line "into:n1 - :n2 " but it doesnt work, showing me that warning in the window log...

any help? Thanks in advance.

   data new;
   input subjid trt fday tday;
    datalines;
    1 1 1 5
    2 1 . 4
    2 1 . 3
    3 1 1 4
    3 2 1 -5
    4 2 1 2
    4 1 1 4
    ;
    run;

     proc sql;
   create table totalx as
   select count(distinct subjid) as n, trt 'Treatment', 'number of subjects condition A' as col0
   into :n1 - :n2                 /*I add this line, but it is not right*/
   from new
   where fday ne .
   group by trt

union

select count(distinct subjid) as n, trt 'Treatment', 'number of subjects condition B' as col0

   from new
   where fday ne . and tday le 0
   group by trt
   order by col0;
   quit;

%put &n1;
%put &n2;

Re: multimacrovariables in sql

michtka — Fri, 08 Jun 2012 13:16:37 GMT

An INTO clause cannot be used in a CREATE TABLE statement.

Re: multimacrovariables in sql

PGStats — Fri, 08 Jun 2012 14:08:13 GMT

Is this what you want (it is not clear what you mean by the value of the treatment)

proc sql;

select count(fday) into :n1- from new group by trt;

quit;

Re: multimacrovariables in sql

PGStats — Fri, 08 Jun 2012 14:32:07 GMT

No, I guess you want this :

proc sql noprint;

select count(distinct subjId) into :n1- from new where fday is not missing group by trt;

quit;

%put &n1;

%put &n2;

Re: multimacrovariables in sql

Ksharp — Mon, 11 Jun 2012 01:51:09 GMT

LOG's information told you you can't use INTO clause in this way, therefore, you need a separated statement to get it ,just as Pierre Gagnon did.

Ksharp