Desktop productivity for business analysts and programmers

transforming a crosstab report into x's, marking categories

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

transforming a crosstab report into x's, marking categories

Hello,

 

I'm trying to do the following:

 

It involves putting people into groups.  The result would be an Excel spreadsheet. 

My data look like this:

 

John Doe                            Ad Sales

Martin Smith                      Ad Sales

Martin Smith                      Marketing

James Stewart                   Ad Sales

James Stewart                   Accounting

Alice Guster                       Accounting

 

The output should be an Excel table:

 

                          Ad Sales             Marketing                 Accounting         

                         _________________________________________                                

John Doe                x

 

Martin Smith           x                         x

 

James Stewart       x                                                                x

 

Alice Guster                                                                             x

 

With x’s on the boxes where a user is part of a group, and no value where they don’t. Anyone think that would be possible?  I created summary tables in EG, but the values in the boxes come out numbers – representing the frequency count in the table. 

 

Thanks,

Joel


Accepted Solutions
Solution
‎11-13-2015 06:08 PM
Grand Advisor
Posts: 17,406

Re: transforming a crosstab report into x's, marking categories

Here's a way to create a table. Simply use Proc Transpose and create a new variable X to denote the relationship.

 

data have;
infile cards dlm=',';
informat name $30. market $30.;
value='X';
input name $ market $;
cards;
John Doe,  Ad Sales
Martin Smith, Ad Sales
Martin Smith, Marketing
James Stewart, Ad Sales
James Stewart, Accounting
Alice Guster, Accounting
;
run;

proc print data=have;
run;

proc sort data=have; by name;
run;

proc transpose data=have out=want(drop=_name_);
by name;
id market;
idlabel market;
var value;
run;

proc print data=want;
run;

View solution in original post


All Replies
Grand Advisor
Posts: 17,406

Re: transforming a crosstab report into x's, marking categories

Apply a format.

proc format;
value x_format
low-high = ''X";
run;
Occasional Contributor
Posts: 8

Re: transforming a crosstab report into x's, marking categories

The issue with that is that the table I produced isn't a dataset, and I don't know how to make it one. 

Grand Advisor
Posts: 17,406

Re: transforming a crosstab report into x's, marking categories

So is your question how to make it a table?
You can apply a format within proc tabulate.
Occasional Contributor
Posts: 8

Re: transforming a crosstab report into x's, marking categories

I'm making progress with proc tabulate.  How would I apply the format?  It's giving me frequency counts.. how do I make cells contain x's rather than positive numbers, wherever the cell displays a positive number?

Occasional Contributor
Posts: 8

Re: transforming a crosstab report into x's, marking categories

Actually, this only gives me what I had before - namely, a table with users cross-tabbed with groups.  So I get counts within the cells but not x's, and no dataset..

 

proc tabulate data=work.users_groups_2;
    class name group;
    table name, group;
run;

Solution
‎11-13-2015 06:08 PM
Grand Advisor
Posts: 17,406

Re: transforming a crosstab report into x's, marking categories

Here's a way to create a table. Simply use Proc Transpose and create a new variable X to denote the relationship.

 

data have;
infile cards dlm=',';
informat name $30. market $30.;
value='X';
input name $ market $;
cards;
John Doe,  Ad Sales
Martin Smith, Ad Sales
Martin Smith, Marketing
James Stewart, Ad Sales
James Stewart, Accounting
Alice Guster, Accounting
;
run;

proc print data=have;
run;

proc sort data=have; by name;
run;

proc transpose data=have out=want(drop=_name_);
by name;
id market;
idlabel market;
var value;
run;

proc print data=want;
run;
Occasional Contributor
Posts: 8

Re: transforming a crosstab report into x's, marking categories

I think this will work..

Occasional Contributor
Posts: 8

Re: transforming a crosstab report into x's, marking categories

It works. Thanks thanks for the help Reeza! I still have a problem on the actual data, because I still have duplicates. So for example, I have


John Doe Accounting

John Doe Accounting


Duplicates haven't been removed, for example, using dedup under proc freq. This seems to be because the underlying values are formatted. So Accounting was Accounting_secretary and Accounting_executive. Basically, John might have multiple roles under accounting. A format is applied to reduce them to accounting, and the resulting duplicates are not getting removed.
Grand Advisor
Posts: 17,406

Re: transforming a crosstab report into x's, marking categories

You'll need to create a new variable that has accounting. Use the PUT() function with the relevant format. Then de-dup it using proc freq or proc sort and then transpose.
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 435 views
  • 0 likes
  • 2 in conversation