Date:         Fri, 2 Mar 2007 12:02:18 -0500
Reply-To:     Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject:      Re: How to create multiple macros using Into : operator ??

the distinct option is used like that:

  select distinct var1, var2 from ...;

It removes duplicates from the output dataset.

Something like:

proc sql;
  create table a as
  select distinct count(sex) as counter, age from sashelp.class;
quit;

That would remove all duplicate age's and add a counter of all obs (before
the duplicates were removed)
By the way: the counter is not 2 ("F" and "M"), it is 19, the number of
obs in the whole dataset. So you could also use any other variable to
count. I think missings would not be counted.

Gerhard





On Fri, 2 Mar 2007 10:24:27 -0600, SAS_learner <proccontents@GMAIL.COM>
wrote:

>Hello guys,
>I am trying to rewrite some part of code in sql for example but I don not
>get what is wrong with SQL code , by the way in the process of writing
code
>I have noticed that I can't use distinct  in second part of sql statement
>like
>
>proc sql ;
>   select pt , distinct ( itt) .................
>quit ;
>at this point SAS complains about distinct part why ??
>
>data header;
> set derive.header (keep = itt);
> where &population = '1';
>run;
>
>proc freq data=header noprint;
> tables itt / missing out=freq0(keep=itt count );
>run;
>data _null_;
> set freq0;
> call symput("tot1",put(count,3.));
> call symput("atot1","(N = "||trim(left(put(count,3.)))||")");
>run;
>%put &tot1 ;
>
>713  proc sql noprint ;
>714     select distinct(count(itt)) as itt_t ,
>NOTE: SCL source line.
>715             "(N= "!!caliculated itt_t !!")"
>                                    -----
>                                    22
>                                    76
>ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (,
*,
>**, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?,
>              AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET,
>LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
>
>ERROR 76-322: Syntax error, statement will be ignored.
>
>716            into : tot1_test  ,
>717                 : atot1_test
>718
>719  from derive.header
>720  where &population = '1' ;
>SYMBOLGEN:  Macro variable POPULATION resolves to itt
>721
>722  %put total is &tot1_test ;
>SYMBOLGEN:  Macro variable TOT1_TEST resolves to      161
>total is      161
>723  %put total_n  is &atot1_test ;
>WARNING: Apparent symbolic reference ATOT1_TEST not resolved.
>total_n  is &atot1_test
>724  quit ;
>
>Thanks for help