transforming a crosstab report into x's, marking categories

[Joel_Wesley_NBCU]

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

[Reeza]

Apply a format.

proc format;
value x_format
low-high = ''X";
run;

[Joel_Wesley_NBCU]

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

[Reeza]

So is your question how to make it a table?
You can apply a format within proc tabulate.

[Joel_Wesley_NBCU]

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?

[Joel_Wesley_NBCU]

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;

[Reeza · Accepted solution]

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;

[Joel_Wesley_NBCU]

I think this will work..

[Joel_Wesley_NBCU]

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.

[Reeza]

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.