topic Re: Frequency Using Proc Sql in SAS Programming

Frequency Using Proc Sql

ArpitSharma — Fri, 13 Oct 2017 17:56:43 GMT

Hello,

I want to do a frequency on a variable using proc sql.

I am doint count(VariableName) and also group by (VariableName) but it does not count the records where there is no value.

I am looking for something equivalent to the missing option in proc freq.

Here is my code:

data have;
input id Gender $;
datalines;
1 M
2 F
3 M
4 .
5 .
6 F
;
run;
proc sql;
select 
Gender
,count(Gender) as frequency
from have
group by Gender
;quit;

The output is:

Gender Frequency

F 2

M 2

I want the output to be like:

Gender Frequency

F 2

M 2

Re: Frequency Using Proc Sql

ArpitSharma — Fri, 13 Oct 2017 18:09:56 GMT

I was able to get this done but I am sure there is a better way to do it: Please suggest

proc sql;
create table freq as
select 
Gender
,sum(freq1,freq2) as frequency
from (	select 
		Gender
		,nmiss(gender) as freq1
		,count(Gender) as freq2
		from have
		group by Gender
		)
;quit;

Re: Frequency Using Proc Sql

PaigeMiller — Fri, 13 Oct 2017 18:14:55 GMT

Better way: use PROC FREQ

Re: Frequency Using Proc Sql

ChrisHemedinger — Fri, 13 Oct 2017 18:23:02 GMT

There is a valid reason to not use PROC FREQ -- maybe -- and that's if your data is in a database and you want the SQL work to push down to the database instead of bringing the data records into SAS.

However, that's not as true as it once was. PROC FREQ can push work down to the database in SAS 9.3 and later, as long as you're using a SAS/ACCESS library engine to get to it.

Re: Frequency Using Proc Sql

Reeza — Fri, 13 Oct 2017 19:28:05 GMT

Change it to PROC FREQ.

Change it to count everything, not just Gender.

In the future post your code in a legible format, the post above requires edits which is a pain.

proc sql;select Gender,count(Gender) as frequencyfrom havegroup by Gender;quit;

Re: Frequency Using Proc Sql

PGStats — Sat, 14 Oct 2017 04:15:25 GMT

The easy way is:

proc sql;
select 
    Gender,
    count(*) as frequency
from have
group by Gender;
quit;

Re: Frequency Using Proc Sql

Ksharp — Sat, 14 Oct 2017 12:33:20 GMT

proc freq data=have;
table gender/ missing;
run;