witch procedure?

[o_o_o_o]

Hi,

I'm looking for a procedure, what help me to create output with a count of data.

I have:

proc format library=work;
value ziel
1 = 'Berlin'
2 = 'London'
3 = 'Paris'
4 = 'Prag'
5 = 'Rom'
;
run;
data work.vorschlag;
input
kunde
vorschlag1
vorschlag2
vorschlag3
;
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;


and I need output:

| V1 | V2 | V3
--------+----+----+----
Berlin | 4 | 1 | 3
London | 4 | 2 | 0
Paris | 3 | 4 | 1
Prag | 3 | 2 | 1
Rom | 6 | 4 | 3

I have make it with data step + proc print, but its not nice.

[CurtisMack]

Well techincally SQL is a procedure 🙂


proc sql;
create table cities as
select *
from (select *
from (select put(vorschlag1,ziel.) as City,count(*) as V1
from vorschlag
where vorschlag1 ne 0
group by City) as T1
full join
(select put(vorschlag2,ziel.) as City,count(*) as V2
from vorschlag
where vorschlag2 ne 0
group by City) as T2
on T1.City = T2.CITY) as T12
full join
(select put(vorschlag3,ziel.) as City,count(*) as V3
from vorschlag
where vorschlag3 ne 0
group by City) as T3
on T12.City = T3.CITY;
quit;

but this is probably uglier than your data step solution. I am also interested if there is a cleaner way of doing this.
Curtis Message was edited by: Curtis Mack

[stateworker]

Try this after you've loaded your data.: (sorry for the ALL CAPs - we just typically program in ALL CAPS).
*** output frequency to a work data set by variable ***;
PROC FREQ DATA=vorschlag;
TABLE vorschlag1 /NOROW NOCOL NOPERCENT NOCUM OUT=vorschlag1;
RUN;
PROC FREQ DATA=vorschlag;
TABLE vorschlag2 /NOROW NOCOL NOPERCENT NOCUM OUT=vorschlag2;
RUN;
PROC FREQ DATA=vorschlag;
TABLE vorschlag3 /NOROW NOCOL NOPERCENT NOCUM OUT=vorschlag3;
RUN;
*** sort to get ready to merge ***;
PROC SORT DATA=VORSCHLAG1; BY VORSCHLAG1; RUN;
PROC SORT DATA=VORSCHLAG2; BY VORSCHLAG2; RUN;
PROC SORT DATA=VORSCHLAG3; BY VORSCHLAG3; RUN;
*** checking data ***;
PROC PRINT DATA=VORSCHLAG1; RUN;
PROC PRINT DATA=VORSCHLAG2; RUN;
PROC PRINT DATA=VORSCHLAG3; RUN;

*** merging ***;
DATA VORSCHLAGALL;
MERGE VORSCHLAG1 (IN=A KEEP=VORSCHLAG1 COUNT RENAME=(VORSCHLAG1=VORSCHLAG COUNT=V1COUNT))
VORSCHLAG2 (IN=B KEEP=VORSCHLAG2 COUNT RENAME=(VORSCHLAG2=VORSCHLAG COUNT=V2COUNT))
VORSCHLAG3 (IN=C KEEP=VORSCHLAG3 COUNT RENAME=(VORSCHLAG3=VORSCHLAG COUNT=V3COUNT));
BY VORSCHLAG;
LABEL V1COUNT='VORSCHLAG1'
V2COUNT='VORSCHLAG2'
V3COUNT='VORSCHLAG3';
RUN;
*** check data ***;
PROC PRINT DATA=VORSCHLAGALL; RUN;
*** clean up data to get rid of missing / change to zero ***;
DATA VORSCHLAGALL;
SET VORSCHLAGALL;
IF VORSCHLAG=0 THEN DELETE; ELSE
IF V1COUNT=. THEN V1COUNT=0; ELSE
IF V2COUNT=. THEN V2COUNT=0; ELSE
IF V3COUNT=. THEN V3COUNT=0; RUN;
*** proc print to output ***;
PROC PRINT DATA=VORSCHLAGALL; RUN;

[GertNissen]

Try something like this

proc format library=work;
value ziel
1 = 'Berlin'
2 = 'London'
3 = 'Paris'
4 = 'Prag'
5 = 'Rom'
;
run;

data work.vorschlag;
input
kunde
vorschlag1
vorschlag2
vorschlag3
;
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;

data transpose(keep=_vorschlag level:);
set vorschlag;
array vorschlag vorschlag1-vorschlag3;
array level {3};
do i = 1 to 3;
_vorschlag=vorschlag;
level{i} = 1;
output;
level{i} = 0;
end;
run;

proc sort data=transpose;
by _vorschlag;
run;

proc means data=transpose NOPRINT sum;
format _vorschlag ziel.;
by _vorschlag;
output out=freq(drop=_type_ _freq_ where=(_vorschlag ne 0) ) sum=;
run;

[Cynthia_sas]

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]