topic Re: Seams simple enough, count(distinct( a:)) with wildcards in SAS Procedures

Seams simple enough, count(distinct( a:)) with wildcards

glmsas — Fri, 29 Apr 2011 17:58:52 GMT

All i'd like to do is to get a distinct count of all my vars that say start with a.
like in the data step i could use something like Keep= a: ; and it would keep all me vars that started with a.

Proc sql doesn't like the a: (like the a*) dos syntax in my SQL

any ideas to create something like this?

thanks

Re: Seams simple enough, count(distinct( a:)) with wildcards

art297 — Fri, 29 Apr 2011 18:10:19 GMT

[pre]
proc sql;
select distinct count(*)
from dictionary.columns
where libname="SASHELP" and memname="CLASS" and
upcase(name) like "A%"
;
quit;
[/pre]

Re: Seams simple enough, count(distinct( a:)) with wildcards

glmsas — Fri, 29 Apr 2011 18:30:03 GMT

ok, it looks as thought that query is saying that there is one col/var in that Claas table that has var that starts with a A.

what i'm tring to get is a count(distinct()) of all the values in that vars that are A%

thanks

Re: Seams simple enough, count(distinct( a:)) with wildcards

art297 — Fri, 29 Apr 2011 20:22:03 GMT

Not sure I understand what you are looking for then. Possibly something like?:
[pre]
proc sql;
select distinct count(*)
from sashelp.class
where upcase(name) like "J%"
;
quit;
[/pre]

Re: Seams simple enough, count(distinct( a:)) with wildcards

data_null__ — Fri, 29 Apr 2011 20:33:38 GMT

[pre]
data class;
retain ab ac ae ag 8;
set sashelp.class;
run;
proc freq data=class nlevels;
ods output nlevels=nlevels;
ods select nlevels;
tables a:;
run;
[/pre]

Re: Seams simple enough, count(distinct( a:)) with wildcards

DBailey — Sat, 30 Apr 2011 00:33:08 GMT

proc sql;
select count(distinct var)
from work.table where var like 'A%';
quit;