Multiple variable frequency in one step

[Johnson_M]

Hi all,

I have the following table structure:

customer	pl1	pl2	pl3
1	a	e	i
2	b	f	j
3	c	g	k
4	d	h	l
5	a	e	i
6	b	f	j
7	c	g	k
8	d	h	l
9	a	e	i
10	b	f	j
11	c	g	k
12	d	h	l

I need to produce the following table:

var	value	no of customers
pl1	a	3
pl1	b	3
pl1	c	3
pl1	d	3
pl2	e	3
pl2	f	3
pl2	g	3
pl2	h	3
pl3	i	3
pl3	j	3
pl3	k	3
pl3	l	3

Can this be done in one step?

I use sas 9.4.

Thanks in advance,

Johnson

[PaigeMiller]

If you want a table (you didn't really say)

proc freq data=have;
   table pl1 pl2 pl3/list;
run;

[Reeza · Accepted solution]

Yes it can be done in one step via PROC FREQ, but you get cleaner results with two steps. 

See the temp table which has the data. 
The want data is the data in a clean, presentable format more in line with what you want. Another option if you're going straight to a report and a single step would be PROC TABULATE. I prefer this though. 

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table sex age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;

Here's another option that works if you're reporting but the layout is slightly different.

proc tabulate data=sashelp.class;
class age sex;
table age sex, N;
run;

---

@Johnson_M wrote:

Hi all,

I have the following table structure:

customer	pl1	pl2	pl3
1	a	e	i
2	b	f	j
3	c	g	k
4	d	h	l
5	a	e	i
6	b	f	j
7	c	g	k
8	d	h	l
9	a	e	i
10	b	f	j
11	c	g	k
12	d	h	l

I need to produce the following table:

var	value	no of customers
pl1	a	3
pl1	b	3
pl1	c	3
pl1	d	3
pl2	e	3
pl2	f	3
pl2	g	3
pl2	h	3
pl3	i	3
pl3	j	3
pl3	k	3
pl3	l	3

Can this be done in one step?

I use sas 9.4.

Thanks in advance,

Johnson

---

[novinosrin]

@Reeza I was just thinking of tabulate with you and grand master @ballardw  in my mind. 🙂 Kudos!

[Johnson_M]

Thank you Reeza,! it worked 🙂

[Ksharp]

data have;
infile cards expandtabs truncover;
input customer	(pl1	pl2	pl3) ($);
cards;
1	a	e	i
2	b	f	j
3	c	g	k
4	d	h	l
5	a	e	i
6	b	f	j
7	c	g	k
8	d	h	l
9	a	e	i
10	b	f	j
11	c	g	k
12	d	h	l
;

proc sql;
select distinct 'pl1' as var length=40,pl1 as value,count(distinct customer) as n_customer
 from have group by pl1
union

select distinct 'pl2' as var length=40,pl1,count(distinct customer) as n_customer
 from have group by pl2
union

select distinct 'pl3' as var length=40,pl1,count(distinct customer) as n_customer
 from have group by pl3
;
quit;