Help using Base SAS procedures

select distinct in PROC SQL

Reply
Contributor spg
Contributor
Posts: 61

select distinct in PROC SQL

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.
Super Contributor
Posts: 578

Re: select distinct in PROC SQL

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?
Super Contributor
Super Contributor
Posts: 365

Re: select distinct in PROC SQL

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
Contributor spg
Contributor
Posts: 61

Re: select distinct in PROC SQL

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

Re: select distinct in PROC SQL

What errors did you get?

SPR
Contributor spg
Contributor
Posts: 61

Re: select distinct in PROC SQL

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.
Contributor spg
Contributor
Posts: 61

Re: select distinct in PROC SQL

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

Re: select distinct in PROC SQL

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.
Super Contributor
Super Contributor
Posts: 365

Re: select distinct in PROC SQL

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
Ask a Question
Discussion stats
  • 8 replies
  • 254 views
  • 0 likes
  • 4 in conversation