Date: Tue, 21 Jun 2005 12:23:36 -0700
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "David L. Cassell" <cassell.david@EPAMAIL.EPA.GOV>
Subject: Re: Flattening File
Content-type: text/plain; charset=US-ASCII
Newbie <oldscot82@YAHOO.COM> wrote:
> IDNUM Type Plan Desc StMonth EndMonth
> 9578 1 AA1 Single Jan-00 Jan-00
> 9578 1 AA2 Double Jan-00 Jan-00
> 9578 1 AA3 Triple Jun-00 Jul-00
> 9578 1 AA4 Quarter Jul-00 Jul-00
> 9578 2 AA5 Fourth Jan-00 Jan-00
> 9578 2 AA5 Fourth Dec-00 Dec-00
> 2222 1 AA7 Fifth Feb-00 Feb-00
> 2222 1 AA7 Fifth Apr-00 May-00
> 2222 1 AA9 Double Jan-00 Jan-00
> 2222 1 AA10 Single Feb-00 Feb-00
> Dear SAS Experts,
> I would like to flatten a file by IDNUM, Type and StMonth. I would
> arrays created for Plan, Desc,
> StMonth and EndMonth. A person can have either Type=1or 2 or both.
> For e.g.. For the above data I would like all the data for the same
> IDNUM, StMonth and Type on the same line.
> All the Jan00 data for IDNUM 9578 and Type 1 would be on one line.
> All the Jun00 data for IDNUM 9578 and Type 1 would be on another line
> and so on and so forth....
> All the Jan00 data for IDNUM 9578 and Type 2 would be on another line.
> Maximum # of entries for the same IDNUM Type and StMonth is 11.
> Thus maximum # of variables for each array would be 11.
> So for Start Month we will have 11 different variables - SM1 to
> Similarly for EndMonth, Plan, Desc.
> Basically I need one entry for each IDNUM Type STMonth
> Hope this is clear. Let me know if you have any questions.
> Much obliged for any assistance.
This *could* be done with arrays. It could also be done using PROC
TRANSPOSE (either a call to PROC TRANSPOSE for each of the three
variables BY your by-variables and then combining the three output
data sets, or else a data step to re-format your input and then a
single PROC TRANSPOSE). This has been done a lot of times in the
But before you look up one of many solutions, perhaps you could write
back to SAS-L and explain why you need this. SAS works better on
'vertical' data, and you're making your data get more 'horizontal'.
If this is just for presentation, SAS has plenty of fine summarization
and reporting procs which might be preferable. If you're doing this
because your boss has pointy hair, then that's a different matter.
David Cassell, CSC
Senior computing specialist