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
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;
The issue with that is that the table I produced isn't a dataset, and I don't know how to make it one.
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?
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;
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;
I think this will work..
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.