topic Re: transforming a crosstab report into x's, marking categories in SAS Enterprise Guide

transforming a crosstab report into x's, marking categories

Joel_Wesley_NBCU — Fri, 13 Nov 2015 22:08:29 GMT

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

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

Reeza — Fri, 13 Nov 2015 22:10:14 GMT

Apply a format.

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

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

Joel_Wesley_NBCU — Fri, 13 Nov 2015 22:13:02 GMT

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

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

Reeza — Fri, 13 Nov 2015 22:28:58 GMT

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

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

Reeza — Fri, 13 Nov 2015 22:36:44 GMT

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;

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

Joel_Wesley_NBCU — Fri, 13 Nov 2015 22:50:38 GMT

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?

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

Joel_Wesley_NBCU — Fri, 13 Nov 2015 22:56:32 GMT

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;

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

Joel_Wesley_NBCU — Fri, 13 Nov 2015 22:59:57 GMT

I think this will work..

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

Joel_Wesley_NBCU — Fri, 13 Nov 2015 23:13:22 GMT

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.

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

Reeza — Fri, 13 Nov 2015 23:16:15 GMT

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.