Hi:
I was thinking more of PROC TABULATE. If you restructure the data in an ARRAY with a DO loop (which I show when the data is read in, but it could be a separate step), then you can capture the position (1,2or3) of the city (VSCHLAG in my code). And then you can use the POSITION variable in a PROC TABULATE:
[pre]
options missing=0;
proc tabulate data=vorschlag_one f=3.;
class vschlag position;
table vschlag all,
position*n all*n ;
keylabel n=' ';
run;
[/pre]
and produces this output in the LISTING window:
[pre]
+----------------------+-----------+---+
| | position | |
| +---+---+---+ |
| | 1 | 2 | 3 |All|
+----------------------+---+---+---+---+
|vschlag | | | | |
+----------------------+ | | | |
|Berlin | 4| 1| 3| 8|
+----------------------+---+---+---+---+
|London | 4| 2| 0| 6|
+----------------------+---+---+---+---+
|Paris | 3| 4| 1| 8|
+----------------------+---+---+---+---+
|Prag | 3| 2| 1| 6|
+----------------------+---+---+---+---+
|Rom | 6| 4| 3| 13|
+----------------------+---+---+---+---+
|All | 20| 13| 8| 41|
+----------------------+---+---+---+---+
[/pre]
More can be done with the table (such as changing or suppressing labels). I added the ALL column and the ALL row to show summary information -- which is easily done with TABULATE. And, of course, in ODS destinations, such as RTF, PDF and HTML, even more can be done with fonts and colors to enhance the table.
cynthia
[pre]
*** Full code;
proc format library=work;
value ziel
1 = 'Berlin'
2 = 'London'
3 = 'Paris'
4 = 'Prag'
5 = 'Rom';
run;
data work.vorschlag_mult (keep=kunde vorschlag1 vorschalg2 vorschalg3)
work.vorschlag_one(keep=kunde position vschlag);
length vschlag $8;
input kunde vorschlag1 vorschlag2 vorschlag3 ;
output work.vorschlag_mult;
** restructure data for second dataset;
array vs(3) vorschlag1 vorschlag2 vorschlag3 ;
do i = 1 to 3 by 1;
vschlag = put(vs(i),ziel.);
position = i;
if vs(i) gt 0 then output vorschlag_one;
end;
format vorschlag1-vorschlag3 ziel.;
datalines;
1 1 0 0
2 5 3 1
3 2 5 0
4 4 0 0
5 5 0 0
6 3 4 5
7 5 0 0
8 3 1 0
9 5 0 0
10 4 5 1
11 2 0 0
12 5 2 0
13 2 4 5
14 1 2 3
15 1 3 5
16 3 5 1
17 2 3 4
18 4 3 0
19 1 5 0
20 5 0 0
;
run;
proc print data=vorschlag_mult;
title 'Multiple cities on a row';
run;
proc print data=vorschlag_one;
title 'One city per obs with position';
run;
options missing=0;
proc tabulate data=vorschlag_one f=3.;
class vschlag position;
table vschlag all,
position*n all*n ;
keylabel n=' ';
run;
[/pre]