Help using Base SAS procedures

Proc Tabulate (repeated values)

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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

                                                                                                                                                              

FamilyFunctionTitleName
AiAnalystJim
AiAnalystRonaldo
AiAnalystZidane
AiAnalystTiger
AiManagerWoods
AiiAccountantSerena
AiiAccountantWilliams
AiimanagerLaura
AiimanagerKristen
BiiiAnalystJohn
BiiiAnalystDavid
BiiiAnalystBackam
Biiisr. analystAnderson
Biiisr. analystMike
Biiisr. analystNike
BivAnalystede
BivAnalystukhulu
BivAnalystnote
Bivsr. analystNoni

Want

                                                                                                                                                             

FamilyFunctionTitleName
AiAnalystJim
AnalystRonaldo
AnalystZidane
AnalystTiger
ManagerWoods
iiAccountantSerena
AccountantWilliams
managerLaura
managerKristen
BiiiAnalystJohn
AnalystDavid
AnalystBackam
sr. analystAnderson
sr. analystMike
sr. analystNike
ivAnalystede
Analystukhulu
Analystnote
sr. analystNoni

Accepted Solutions
Solution
‎09-09-2013 03:55 PM
Super User
Posts: 19,770

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’s so great about SPANROWS? - The SAS Training Post

View solution in original 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: 45

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: 11,343

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: 19,770

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’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: 45

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: 19,770

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 349 views
  • 3 likes
  • 5 in conversation