BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Chris_Loke
Fluorite | Level 6

Hello!

 

I have a dataset (simplified example) with only one variable. 

 

Data have;

input class$;

A

A

A

B

B

C

C

C

C

D

 

I would like to calculate the percentages and counts of each variable in my dataset using PROC SQL.

 

Output would be similiar to proc freq:

 

Desired output should be ;

 

class

Frequency

Percent

A

3

30

B

2

20

C

4

40

D

1

10

 

How can I do this with PROC SQL?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Hi @Chris_Loke  I copy pasted from online for you 🙂

 

Data have;
input class$;
cards;
A
A
A
B
B
C
C
C
C
D
;

proc sql;
create table want as
select class,count(class) as frequency,calculated frequency/(select count(*) from have) as pct format=percent.
from have
group by class;
quit;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

Hi @Chris_Loke  I copy pasted from online for you 🙂

 

Data have;
input class$;
cards;
A
A
A
B
B
C
C
C
C
D
;

proc sql;
create table want as
select class,count(class) as frequency,calculated frequency/(select count(*) from have) as pct format=percent.
from have
group by class;
quit;
yabwon
Onyx | Level 15

Hi,

How about like that:

 

proc sql;
select a.class, count(a.class) as countclass,  (calculated countclass/b.all)*100 as percent
from
have as a, (select count(class) as all from have) as b
group by a.class
;
quit;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Reeza
Super User
Why not use PROC FREQ which does the calculations for you by default?
PaigeMiller
Diamond | Level 26

Why not use PROC FREQ which does the calculations by default, and doesn't have the drawback that missing values will cause the WRONG percentages to be calculated by PROC SQL (unless missing is a valid category). PROC FREQ gives you a choice how to handle missings, PROC SQL does not. PROC FREQ easily generalizes to two-way tables while PROC SQL does not. There are so many benefits to using PROC FREQ, I would not recommend PROC SQL for this purpose.

--
Paige Miller
Panagiotis
SAS Employee

Agree with @PaigeMiller 

 

Here is a quick example. I added missing values in the original data set and included the out= option to create new tables if you need them.

 

Data have;
infile datalines missover;
input class$;
datalines;
A
A
A
B
B
C
C
C
 
C
 
D
 
;

run;

/*Ignore missing in frequency. Will show how many are missing in the output table but won't count them in the percentage*/
title "Ignore Missing in Frequency";
proc freq data=have;
	tables class /out=newtbl1(where=(class ne " ")) /*create a new table and remove the missing row from it*/
				  nocum /*remove cumulative freq*/;
run;

/*Include missing in frequency and will include missing values in the percentage*/
title "Include Missing in Frequency";
proc freq data=have;
	tables class /out=newtbl2 /*create a new table*/
				  missing /*remove cumulative freq*/
				  nocum /*include missing values in frequency*/;
run;
title;

 

HTML Results

results.jpg

 

 

- Peter

Chris_Loke
Fluorite | Level 6

Thank you for replying!

 

The thing is that I have ten different datasets for ten different years that I want to perform my counts on. I want join all the years togheter in to one table and the export it to excel.  

 

If this is possible to do with Proc Freq I would be happy to learn it!

Reeza
Super User

Both FREQ and SQL take only one input so it doesn't' matter which you use. Unless you create a view first and then run the SQL or FREQ on the view. Either way, FREQ would still be more efficient in terms of processing time, but you already have a solution so now it's most efficient to use that.

 


@Chris_Loke wrote:

Thank you for replying!

 

The thing is that I have ten different datasets for ten different years that I want to perform my counts on. I want join all the years togheter in to one table and the export it to excel.  

 

If this is possible to do with Proc Freq I would be happy to learn it!


 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 8 replies
  • 22041 views
  • 8 likes
  • 6 in conversation