SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

sas character and numeric variables frequency count and analysis (like powerpivot result in excel)

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

sas character and numeric variables frequency count and analysis (like powerpivot result in excel)

Hi, I have two columns, one is time variable, another one is the character variable. My data looks like below. I would like to get the frequency resuts like the below one. Could anyone show me the code to do this?

My original data:

Time Character
1A
2B
3B
4C
5D
1A
2C
3B
4D
5A
1B
2C
3D
1C
2D
3B
4A
5

A

 

I would like to get the result like:

Count of CharacterColumn Labels    
Row LabelsABCDGrand Total
1211 4
2 1214
3 3 14
41 113
52  13
Grand Total554418

 

I tried use proc freq, but only got the seperate statistical results for each variable. 

 

Could anyone show me how to get the result like the second table?

 

Many appreciation.


Accepted Solutions
Solution
‎06-21-2017 07:12 AM
Super User
Posts: 6,962

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

This is done with proc freq:

data have;
infile cards dlm='09'x;
input time character $;
cards;
1	A
2	B
3	B
4	C
5	D
1	A
2	C
3	B
4	D
5	A
1	B
2	C
3	D
1	C
2	D
3	B
4	A
5	A
;
run;

proc freq data=have;
tables time*character / nopercent nocum nocol norow;
run;

Note how example data is presented in a data step for easy recreation (just needs copy/paste and run).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-21-2017 07:12 AM
Super User
Posts: 6,962

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

This is done with proc freq:

data have;
infile cards dlm='09'x;
input time character $;
cards;
1	A
2	B
3	B
4	C
5	D
1	A
2	C
3	B
4	D
5	A
1	B
2	C
3	D
1	C
2	D
3	B
4	A
5	A
;
run;

proc freq data=have;
tables time*character / nopercent nocum nocol norow;
run;

Note how example data is presented in a data step for easy recreation (just needs copy/paste and run).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

Thank you very much for your solution. I already got the result.

By the way, when I tried to look at the percent, it only desplays two decimals. 

Like : Percent 0.01

 

Could you tell me how could I modift the decinmal, to get the result like 

Percent 0.0985?

 

Thank you

Super User
Posts: 6,962

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

Since one can't change the format of the percentages (only the format for the frequency values can be set), I suggest you use the out= option in the tables statement and create a customized report from that dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

Thanks for your help.

 

I have a further question. How could I draw line graphs for the frequency variables for each category?

I tried to emport the data to excel, but could not do it.

 

Thank you.

Super User
Posts: 10,521

Re: sas character and numeric variables frequency count and analysis (like powerpivot result in exce

Create a data set with the OUT= option on the Tables statement in freq and then plot the result with SGPLOT.

 

proc freq data=have;
tables time*character / nopercent nocum nocol norow  out=want;
run;

 

Since there are a number of possibly plots at that point you should start a new thread with data of the data set to plot if you have problems.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 240 views
  • 0 likes
  • 3 in conversation