It will get you the unique rows for variables specified in select clause. It is similar to 'group by' but no summary statistics computed. select distinct race, subno, sex from have the result of this query: 1  black male 2  black female 3  latin male 4 black male 5 latin female 6 oriental male Using the result of this sub-query, you can get the counts by race. ... View more

Thanks Hai.kuo, in terms of the table you are right, but the "where condition" cant change in my code. I found a possible solution keeping my where condition, adding an extra condition:      proc sql;    create table totalx as    select count(distinct subjid) as n, trt 'Treatment', 'number of subjects condition A' as col0    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;    proc transpose data=totalx out=totalt (drop=_name_) prefix=trt;    by col0;    id trt;    var n;    run; ... View more

OK, proc sql it is: proc sql;   select race, sum(sex='male') as male, sum(sex='female') as female, count(race) as total   from have   group by race; quit; Haikuo ... View more

You can get them assigned to a series of macro variables this way, but make sure you can recreate the order to match them up later with the proper treatments. proc sql noprint; select count(distinct subjid) into :ntrt1 - :ntrt2   group by treatment   order by treatment ; There are two issues with this. First you will get a warning about grouping by a variable that is not included in the selection list. You can either also pull out the treatments into another macro variable array (if it is useful for you), or if you do not need it just shove into a dummy macro variable to suppress the warning. select treatment,count(distinct subjid)    into dummy,:ntrt1 - :ntrt2   group by treatment   order by treatment ; Notice that the list of macro variables is after the INTO keyword and separated by commas just like the list of values that starts the select statement. The other problem is how many macro variables will you need?  If you are creating generic code then that might not be known in advance.  You can give it an extreme value for the upperrange in the INTO clause and it will only create the macro variables that it needs.  You can then reference the automatic macro variable SQLOBS to find how many groups there were. Combining these we could create two macro variable arrays (TRTxx and NTRTxx) and the count (NTRTGRP) using this statement. select      treatment      ,count(distinct subjid)   into :trt1 - :trt200      , :ntrt1 - :ntrt200   group by treatment   order by treatment ; %let ntrtgrp = &sqlobs ; ... View more

The & lets you read character strings with embedded single blanks.  In your example the AE variable. The : tells SAS not to take the informat literally.  Instead of reading 14 characters for the treatment code 'placebo' it will stop when it sees the space. This will prevent it from "eating" (a very techinical term) the beginning of the date variable. I usually find that it is better to use a LENGTH statement or ATTRIB statements to define the variables BEFORE the input statement.  This also has the advantage of allowing you to set the order that the variables will appear in your dataset.  Currently DAT and DATE will appear first because they are mentioned first (in the FORMAT statement). You can also assign INFORMATs for the variables that need them (such as date and time values). Then the input statement is much easier.  For your data the only thing that you would need special in your INPUT statement would be the & because of the embedded delimiters in the value of AE variable.  You might want to used a delimiter other than space. data new4; length obs subjid pp 8 ae $14 dat 8 trt $10 date 8 treatment $10; informat dat date9. date mmddyy10.; format dat date yymmdd10.; input obs subjid pp ae & dat trt date treatment; datalines;   1       1000000134     0  headache       23jan63       placebo   10/10/2005  drug   1       1000000234     0  lung left pain       23jan34       placebo   10/10/1995  drug   1       1000000134     0  headache       23jan63       placebo   10/10/2005  drug ; ... View more