Solved
Contributor
Posts: 48

# Proc Tabulate (repeated values)

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

Accepted Solutions
Solution
‎09-09-2013 03:55 PM
Super User
Posts: 23,676

## Re: Proc Tabulate (repeated values)

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&#8217;s so great about SPANROWS? - The SAS Training Post

All Replies
Super Contributor
Posts: 307

## Re: Proc Tabulate (repeated values)

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;

Contributor
Posts: 48

## Re: Proc Tabulate (repeated values)

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;

Super User
Posts: 13,507

## Re: Proc Tabulate (repeated values)

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.

Solution
‎09-09-2013 03:55 PM
Super User
Posts: 23,676

## Re: Proc Tabulate (repeated values)

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&#8217;s so great about SPANROWS? - The SAS Training Post

Super Contributor
Posts: 307

## Re: Proc Tabulate (repeated values)

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.

Super Contributor
Posts: 307

## Re: Proc Tabulate (repeated values)

I forgot to add the column level specification . . .

proc tabulate data=have;

class family function title name;

table family*function*title*name, n;

run;

Contributor
Posts: 48

## Re: Proc Tabulate (repeated values)

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,

Contributor
Posts: 70

## Re: Proc Tabulate (repeated values)

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;

Super User
Posts: 23,676

## Re: Proc Tabulate (repeated values)

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

🔒 This topic is solved and locked.