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 |
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
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;
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;
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.
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
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.
I forgot to add the column level specification . . .
proc tabulate data=have;
class family function title name;
table family*function*title*name, n;
run;
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,
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;
FYI You can also use tagsets and have proc tabulate output go directly to excel.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.