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
0
F 2
M 2
I want the output to be like:
Gender Frequency
2
F 2
M 2
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;
Better way: use PROC FREQ
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.
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;
The easy way is:
proc sql;
select
Gender,
count(*) as frequency
from have
group by Gender;
quit;
proc freq data=have;
table gender/ missing;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.