BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Greek
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
Fugue
Quartz | Level 8

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;

Greek
Obsidian | Level 7

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;

ballardw
Super User

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.

Reeza
Super User

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

Fugue
Quartz | Level 8

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.

Fugue
Quartz | Level 8

I forgot to add the column level specification . . .

proc tabulate data=have;

class family function title name;

table family*function*title*name, n;

run;

Greek
Obsidian | Level 7

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,

yaswanthj
Calcite | Level 5

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;

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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