Solved
Contributor
Posts: 34

# 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 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

I would like to get the result like:

 Count of Character Column Labels Row Labels A B C D Grand Total 1 2 1 1 4 2 1 2 1 4 3 3 1 4 4 1 1 1 3 5 2 1 3 Grand Total 5 5 4 4 18

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: 9,878

## 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
How to convert datasets to data steps
How to post code

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

## 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
How to convert datasets to data steps
How to post code
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: 9,878

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 34

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

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: 13,297

## 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.

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