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.
create table a as
select distinct count(sex) as counter, age from sashelp.class;
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.
On Fri, 2 Mar 2007 10:24:27 -0600, SAS_learner <proccontents@GMAIL.COM>
>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
>I have noticed that I can't use distinct in second part of sql statement
>proc sql ;
> select pt , distinct ( itt) .................
>at this point SAS complains about distinct part why ??
> set derive.header (keep = itt);
> where &population = '1';
>proc freq data=header noprint;
> tables itt / missing out=freq0(keep=itt count );
> set freq0;
> call symput("tot1",put(count,3.));
> call symput("atot1","(N = "||trim(left(put(count,3.)))||")");
>%put &tot1 ;
>713 proc sql noprint ;
>714 select distinct(count(itt)) as itt_t ,
>NOTE: SCL source line.
>715 "(N= "!!caliculated itt_t !!")"
>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
>719 from derive.header
>720 where &population = '1' ;
>SYMBOLGEN: Macro variable POPULATION resolves to itt
>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