Date: Thu, 16 Aug 2001 15:10:17 -0400 "Diskin, Dennis" "SAS(r) Discussion" "Diskin, Dennis" Re: Filling out the group, SQL or Datastep? To: Richard DeVenezia text/plain

Richard,

If I understand the problem correctly: it would be simpler if you just wanted all companies to be represented on each graph. Since you want a fixed number of companies which is determined by the data, it will take several passes to build an augmented file.

1. Determine the NUMCOMP number of companies wanted.

data xyz; length company \$ 8; input class1 class2 class3 company measure; cards; 1 3 1 good 1 1 3 1 good 1 1 3 3 bad 2 1 3 3 good 1 1 3 3 ugly 4 1 5 1 bad 1 1 5 1 some 1 ; OBS COMPANY CLASS1 CLASS2 CLASS3 MEASURE

1 good 1 3 1 1 2 good 1 3 1 1 3 bad 1 3 3 2 4 good 1 3 3 1 5 ugly 1 3 3 4 6 bad 1 5 1 1 7 some 1 5 1 1

data _null_; set xyz end=eof; by class1 class2 class3 company; retain maxcomp 0; if last.company; ncomp+1; if last.class3; maxcomp = max(maxcomp,ncomp); ncomp = 0; if eof then call symput('NUMCOMP',trim(left(put(maxcomp,6.)))); run;

2. Add dummy records to the data for each class combo wit less than the maximum number of companies.

data xyz2; set xyz; by class1 class2 class3 company; drop ncomp icomp; output; if last.company; ncomp+1; if last.class3; measure = 0; do icomp = ncomp+1 to &NUMCOMP; company = 'D_' || left(put(icomp,8.)); output; end; ncomp = 0; run;

OBS COMPANY CLASS1 CLASS2 CLASS3 MEASURE

1 good 1 3 1 1 2 good 1 3 1 1 3 D_2 1 3 1 0 4 D_3 1 3 1 0 5 bad 1 3 3 2 6 good 1 3 3 1 7 ugly 1 3 3 4 8 bad 1 5 1 1 9 some 1 5 1 1 10 D_3 1 5 1 0

hth, Denis Diskin

> -----Original Message----- > From: Richard DeVenezia [SMTP:radevenz@IX.NETCOM.COM] > Sent: Thursday, August 16, 2001 2:11 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Filling out the group, SQL or Datastep? > > I'm drawing a blank today... > > Suppose I have a dataset xyz with five variables: > class1, class2, class3, company, measure > > used as such > > proc gchart data=xyz; > by class1 class2; > vbar company / discrete group=class3 sumvar=measure; > > However, every class1,class2,class3 combination does not contain the same > number of companies. > > I want to ensure consistent patterning, so I need every > class1,class2,class3 > combination to contain the minimum number of companies. The 'dummy' > company > rows would have the measure set to zero or missing so a bar will not > appear > for it. > > e.g. > class1 has value A or B > class2 has value 0 or 1 > class3 has value good, bad, ugly > companies has values a1,a2,a3,a4,a5 (when class3=good), b1,...,b6 (when > class3=bad) and u1,...,u31 (when class3=ugly) > in a given drill class1,2,3 each present company appears only once > (nodups) > > I need to add rows to xyz so that each class1,2,3 combination contains the > fewest number of companies to cover all the situations. > > -- > Richard DeVenezia - SAS Macros and AF Tools > http://www.devenezia.com

Back to: Top of message | Previous page | Main SAS-L page