Building reports & grouping variables

[deleted_user]

Hi All,
I am trying to build a sas report where I need to group affiliation codes and present all of them in a single row.
For example
my data set contain the following fields
ID name affliation_name
1 XYZ abc
1 XYZ cde
2 LMN abc
2 LMN cde
2 LMN ijk
3 RST cde
3 RST ijk

I need it to be transformed like this
ID name affiliation_name1 affiliation_name2 affiliation_name3
1 XYZ abc cde
2 LMN abc cde ijk
3 RST cde ijk

Also the affiliation name should be renamed to 123 456 789
I know I can use proc transpose but with this the first affiliation name would go into the first affiliation field. Any ideas.

[sbb]

You can prepare (sort) your data first then TRANSPOSE or TRANSPOSE and then format your data, if you are concerned about the SAS-generated variable order. Otherwise it's unclear to me what you mean by "the first affiliation name would go into the first affiliation field."

If you have made an attempt, by all means, share your code and the undesirable results, for forum subscriber feedback and guidance.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Here is my data set Aff_name1
ID name Affiliation_name
1 abc ATT
1 abc Tmobile
1 abc verizon
2 def ATT
2 def Tmobile
3 ghi Verizon

When I do proc transpose data = Aff_name1 out = Aff_name2;
by ID name ;
var Affiliation_name;
run;

It returns me the following output file Aff_name2
ID name Col1 Col2 Col3
1 abc ATT Tmobile Verizon
2 def ATT Tmobile
3 ghi Verizon

Instead I want the output as
ID name Col1 Col2 Col3
1 abc ATT Tmobile Verizon
2 def ATT Tmobile
3 ghi Verizon

Any suggestions on how I can limit which value needs to go into which column.
Thanks,
APS

[Cynthia_sas]

Hi:
Somehow, I think the indenting that you want to use is getting lost in the forum posting mechanism. For more information on how to post code and maintain indenting in future posts, this is useful information:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

What I think you're saying is that you are getting this:
[pre]
ID name Col1 Col2 Col3
1 abc ATT Tmobile Verizon
2 def ATT Tmobile
3 ghi Verizon
[/pre]

with "mixed" information in the COL1 variable and that's not what you want.

I think you mean that you want this:
[pre]
ID name Col1 Col2 Col3
1 abc ATT Tmobile Verizon
2 def ATT Tmobile
3 ghi Verizon
[/pre]

So that COL1 is ALWAYS ATT and COL2 is ALWAYS Tmobile and COL3 is ALWAYS Verizon. Is that what you mean???

Is the above the report you want or is it the structure of the dataset that you want? Generally, if you wanted to see a report grouped in such a way (so that the Affiliation_name values were going across the top of the report), you might use PROC TABULATE or PROC REPORT and then in the cells underneath the Affiliation_name, you might see some number, such as a count, a percent or some other statistic, such as number of customers, amount of sales, number of minutes, etc. Simple counts (the N statistic) are shown in the program below without using PROC TRANSPOSE.

cynthia
[pre]
data mydata;
infile datalines;
input ID name $ Affiliation_name $;
return;
datalines;
1 abc ATT
1 abc Tmobile
1 abc Verizon
2 def ATT
2 def Tmobile
3 ghi Verizon
;
run;

ods listing;
proc tabulate data=mydata f=comma8.;
class name affiliation_name;
table name all,
affiliation_name all;
run;

proc report data=mydata nowd;
column name (affiliation_name,n) n;
define name / group;
define affiliation_name / across;
define n / f=comma8.;
rbreak after / summarize;
run;
[/pre]

[deleted_user]

Yeah you have decsribed correctly what I want in the out put. I want instead on number or counts. Can you please advise on how to do that.

[deleted_user]

I meant I wanted the actual values to be displayed instead of number or counts

[Cynthia_sas]

Hi:
Probably the most straightforward way to do what you want is to use a DATA step program and ARRAY processing to create the variables in a dataset in the order you want. One quick thing to do might be to use an INFORMAT to determine the proper column "number"...something like this:
[pre]
proc format;
invalue aff 'ATT' = 1
'Tmobile' = 2
'Verizon' = 3
other = .;
run;
[/pre]

And then, in your program, you could set an index variable using the INFORMAT like this:
[pre]
j = put(affiliation_name, aff.);

array_name(j) = affiliation_name;
[/pre]

In the above code snippet, the variable J will take on the value of 1, 2 or 3, depending on the defined informat values. Then the reference array_name(j) assumes that you are using an array referencing technique to assign the current observation's value for AFFILIATION_NAME to the correct array variable.

You would also need to use BY group processing and FIRST. and LAST. variables in order to make sure that you only output one observation at the end of the group and that you initialized the new array variables to blanks at the beginning of each new group.

For more information about array processing and to learn about how to write a DATA step program that uses arrays, refer to some of these papers or documentation topics:
http://support.sas.com/rnd/papers/sgf07/arrays1780.pdf
http://support.sas.com/forums/thread.jspa?messageID=13408㑠
http://www2.sas.com/proceedings/sugi30/242-30.pdf
http://www2.sas.com/proceedings/sugi27/p066-27.pdf
http://www2.sas.com/proceedings/sugi24/Posters/p215-24.pdf
http://support.sas.com/resources/papers/proceedings09/155-2009.pdf
http://www2.sas.com/proceedings/sugi24/Advtutor/p48-24.pdf
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002299816.htm

cynthia