BookmarkSubscribeRSS Feed
ArpitSharma
Fluorite | Level 6

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

6 REPLIES 6
ArpitSharma
Fluorite | Level 6

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;
PaigeMiller
Diamond | Level 26

Better way: use PROC FREQ

--
Paige Miller
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Reeza
Super User

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;

delete_sql_count.JPG

PGStats
Opal | Level 21

The easy way is:

 

proc sql;
select 
    Gender,
    count(*) as frequency
from have
group by Gender;
quit;
PG
Ksharp
Super User
proc freq data=have;
table gender/ missing;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 18144 views
  • 3 likes
  • 6 in conversation