topic Re: Proc Tabulate (repeated values) in SAS Procedures

Proc Tabulate (repeated values)

Greek — Mon, 09 Sep 2013 18:12:25 GMT

Hello,

I am trying to recreate a pivot table in excel. I have used proc tabulate and I managed to get the desired results, but the table looks like the one below. I am trying to delete the repeated values of "family" and "function" without deleting the entire row. does anybody know how to do this?

Have

Family	Function	Title	Name
A	i	Analyst	Jim
A	i	Analyst	Ronaldo
A	i	Analyst	Zidane
A	i	Analyst	Tiger
A	i	Manager	Woods
A	ii	Accountant	Serena
A	ii	Accountant	Williams
A	ii	manager	Laura
A	ii	manager	Kristen
B	iii	Analyst	John
B	iii	Analyst	David
B	iii	Analyst	Backam
B	iii	sr. analyst	Anderson
B	iii	sr. analyst	Mike
B	iii	sr. analyst	Nike
B	iv	Analyst	ede
B	iv	Analyst	ukhulu
B	iv	Analyst	note
B	iv	sr. analyst	Noni

Want

Family	Function	Title	Name
A	i	Analyst	Jim
		Analyst	Ronaldo
		Analyst	Zidane
		Analyst	Tiger
		Manager	Woods
	ii	Accountant	Serena
		Accountant	Williams
		manager	Laura
		manager	Kristen
B	iii	Analyst	John
		Analyst	David
		Analyst	Backam
		sr. analyst	Anderson
		sr. analyst	Mike
		sr. analyst	Nike
	iv	Analyst	ede
		Analyst	ukhulu
		Analyst	note
		sr. analyst	Noni

Re: Proc Tabulate (repeated values)

Fugue — Mon, 09 Sep 2013 18:53:34 GMT

Basic code for Proc Tabulate to get the desired output would be like this. Because no summary statistics are requested, Proc Tabulate will default to a count.

proc tabulate data=have ;

class family function title name ;

table family*function*title*name ;

run;

Re: Proc Tabulate (repeated values)

Greek — Mon, 09 Sep 2013 19:06:23 GMT

Hello Fugue,

This is the code that I used. It created the dataset that I have, but the values of Family and function are still repeated at every row... Any suggestions?

data have;

input family $ function $ title $ name $;

datalines;

A i Analyst Jim

A i Analyst Ronaldo

A i Analyst Zidane

A i Analyst Tiger

A i Manager Woods

A ii Accountant Serena

A ii Accountant Williams

A ii manager Laura

A ii manager Kristen

B iii Analyst John

B iii Analyst David

B iii Analyst Backam

B iii Analyst Anderson

B iii Analyst Mike

B iii Analyst Nike

B iv Analyst ede

B iv Generalist ukhulu

B iv Generalist note

B iv Generalist Noni

run;

proc tabulate data=have out=want;

class family function title name;

table family*function*title*name;

run;

proc print data=want;

run;

Re: Proc Tabulate (repeated values)

ballardw — Mon, 09 Sep 2013 19:30:46 GMT

So why not use the Tabulate output directly instead of printing the output dataset?

The other option is going to require post processing the "want" data to remove values and then print. I'm not sure what the advantage to that path would be.

Re: Proc Tabulate (repeated values)

Reeza — Mon, 09 Sep 2013 19:55:27 GMT

Do want the output displayed that way or the data from out= to have missing observations?

Generally the second option (having missing values for the repeats) isn't considered good data structure.

You can get the output the way you like it using proc report for display.

What’s so great about SPANROWS? - The SAS Training Post

Re: Proc Tabulate (repeated values)

Fugue — Mon, 09 Sep 2013 20:34:01 GMT

As both ballardw and reeza suggest, Proc Tabulate will print the output essentially the way that you want (as you set out in your original post).

However, judging by your second post, it seems that you are trying to create a dataset that looks like the printed output from proc tabulate. I'm not really clear on why you need the dataset to have that structure.

Re: Proc Tabulate (repeated values)

Fugue — Mon, 09 Sep 2013 20:40:49 GMT

I forgot to add the column level specification . . .

proc tabulate data=have;

class family function title name;

table family*function*title*name, n;

run;

Re: Proc Tabulate (repeated values)

Greek — Tue, 10 Sep 2013 13:04:48 GMT

Hello all,

thank you very much for your responses! To answer your question, someone needs to manipulate the results after I am done. Copying and pasting the original output to an excel spreadsheet is an option, but I was hoping to create a data set and export it to excel instead of doing it manually.

Fugue, the n solved the problem thank you very much.

Reeza, thank you for the post, this is very helpful!

Regards,

Re: Proc Tabulate (repeated values)

yaswanthj — Tue, 10 Sep 2013 14:23:41 GMT

Another way to do this using data step. this may help full to some one...

data have;

input @1 Family $1 @5 Function $3. @9 Title $12. @26 Name $10.;

cards;

A i Analyst Jim

A i Analyst Ronaldo

A i Analyst Zidane

A i Analyst Tiger

A i Manager Woods

A ii Accountant Serena

A ii Accountant Williams

A ii manager Laura

A ii manager Kristen

B iii Analyst John

B iii Analyst David

B iii Analyst Backam

B iii sr. analyst Anderson

B iii sr. analyst Mike

B iii sr. analyst Nike

B iv Analyst ede

B iv Analyst ukhulu

B iv Analyst note

B iv sr. analyst Noni

run;

proc sort data = have;

by family function;

run;

data want(drop = family1 a b function1);

set have;

length family1 function1 $20.;

by family function;

a = first.family;

b = first.function;

retain family function title name;

if a = 1 then do;

family1 = family;

end; else family1 = '';

if b = 1 then do;

function1 = function;

end; else function= '';

family = family1;

function = function1;

run;

ods pdf file='C:\Documents and Settings\yaswanth.j\Desktop\test\want.pdf';

proc print data = want;

run;

ods pdf close;

Re: Proc Tabulate (repeated values)

Reeza — Tue, 10 Sep 2013 15:05:55 GMT

FYI You can also use tagsets and have proc tabulate output go directly to excel.