topic Re: Multiple variable frequency in one step in SAS Programming

Multiple variable frequency in one step

Johnson_M — Thu, 23 Jan 2020 18:11:22 GMT

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

Re: Multiple variable frequency in one step

PaigeMiller — Thu, 23 Jan 2020 18:22:47 GMT

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

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

Re: Multiple variable frequency in one step

Reeza — Thu, 23 Jan 2020 18:24:38 GMT

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

Re: Multiple variable frequency in one step

novinosrin — Thu, 23 Jan 2020 18:28:39 GMT

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

Re: Multiple variable frequency in one step

Johnson_M — Fri, 24 Jan 2020 18:55:14 GMT

Thank you Reeza,! it worked 🙂

Re: Multiple variable frequency in one step

Ksharp — Sat, 25 Jan 2020 09:30:00 GMT

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;