topic Re: select distinct in PROC SQL in SAS Procedures

select distinct in PROC SQL

spg — Mon, 21 Mar 2011 18:38:08 GMT

Hi,

I want to use the following code to create counts of groups. My only problem is that there are more than a 100 distinct types. Is there a smart way to do it?

proc sql;
create table a as
select distinct type1, type2, type3, count (*) as count
from b
group by type1, type2, type3;
quit;

Thanks.

Re: select distinct in PROC SQL

DBailey — Mon, 21 Mar 2011 19:14:04 GMT

That won't work if you're trying to get the number of combinations rather than the number of records in each combination. What does your data look like?

Re: select distinct in PROC SQL

SPR — Mon, 21 Mar 2011 19:50:32 GMT

Hello SPG,

This is a solution:
[pre]
proc SQL;
select COUNT(distinct name) as nt into :nt
from SASHELP.vcolumn
where LIBNAME="WORK" and MEMNAME="B";
%let nt=%TRIM(&nt);
select name as name into :t1-:t&nt
from SASHELP.vcolumn
where LIBNAME="WORK" and MEMNAME="B";
quit;
%macro a;
%local i;
%do i=1 %to &nt;
proc SQL;
create table a&i as
select distinct &&t&i, COUNT(*) as count
from b
group by &&t&i
;quit;
%end;
%mend a;
%a
[/pre]
Sincerely,
SPR

Re: select distinct in PROC SQL

spg — Tue, 22 Mar 2011 20:15:26 GMT

Thanks much SPR! Though this is giving me some errors, I get the general idea and am working to use it for my purposes.

Re: select distinct in PROC SQL

SPR — Tue, 22 Mar 2011 20:48:22 GMT

What errors did you get?

SPR

Re: select distinct in PROC SQL

spg — Fri, 25 Mar 2011 14:09:37 GMT

So sorry, I completely overlooked this.
Here is the error

WARNING: INTO Clause :t1 through :t0 does not specify a valid sequence of macro variables.

Re: select distinct in PROC SQL

spg — Fri, 25 Mar 2011 14:10:37 GMT

WARNING: INTO Clause :t1 through :t0 does not specify a valid sequence of macro variables.
NOTE: No rows were selected.
4093 quit;

Re: select distinct in PROC SQL

sbb — Fri, 25 Mar 2011 14:13:57 GMT

Check your PROC SQL output (turn on OPTIONS MACROGEN SYMBOLGEN; to start) -- no selected rows, so &nt resolves to "0" (zero).

Scott Barry
SBBWorks, Inc.

Re: select distinct in PROC SQL

SPR — Fri, 25 Mar 2011 15:05:55 GMT

Hello SPG,

Pay attention on the following code:

where LIBNAME="WORK" and MEMNAME="B";

You have to use your library name instead of WORK, and its name should be in capital letters. The same is related to dataset name B.

Sincerely,
SPR