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

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

JinboZhao — Wed, 21 Jun 2017 10:31:03 GMT

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.

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

Kurt_Bremser — Wed, 21 Jun 2017 11:09:53 GMT

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

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

JinboZhao — Wed, 21 Jun 2017 11:23:37 GMT

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

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

Kurt_Bremser — Wed, 21 Jun 2017 11:41:49 GMT

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.

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

JinboZhao — Wed, 21 Jun 2017 15:11:19 GMT

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.

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

ballardw — Wed, 21 Jun 2017 18:08:29 GMT

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.