Date:         Wed, 28 Mar 2001 17:27:19 -0500
Reply-To:     sander.post@STATCAN.CA
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         sander.post@STATCAN.CA
Subject:      Re: Two SQL(?) Questions
Comments: To: yamanishi@MEDIAONE.NET
Content-Type: text/plain; charset="iso-8859-1"

-----Original Message-----
From: David Yamanishi [mailto:yamanishi@NOSPAM.MEDIAONE.NET]
Sent: March 28,2001 2:29 PM

The [edited] questions:

First, the simple problem:

Count the number of 2's per unit and add it as a variable to the 1's
record (dropping all the 2 observations).

Next, the complex problem:

Or, alternatively, of fixed record length where the length is set
automatically according to the number of 2's in the unit with the most
people and the variables filled in for each 2 that's actually present
for each unit.

Thank you,

David

----

Complex answer first, of course - though I'm not doing this in SQL.  The
following reads in your data, and puts in all the variables (with names
COL1-COLnn).

data temp;
  input unit person var;

  cards ;
1       1       6
1       2       18
2       1       19
2       2       20
2       2       14
2       2       11
3       1       12
3       2       9
3       2       4
;
run;

proc transpose data=temp out=trantemp;
  by unit;
  var var;
  run;

---

a proc print shows the data looks like:

                      Obs    unit    _NAME_    COL1    COL2    COL3    COL4

                       1       1      var        6      18       .       .
                       2       2      var       19      20      14      11
                       3       3      var       12       9       4       .

Once you have the data in this form, I imagine the simple question pretty
much answers itself.

Cheers,

Sander