I'll presume you have a good reason for doing this. Your target structure
is one which is generally disadvantageous.
PROC TRANSPOSE is often used for such a conversion. But because you have
three vectors to flatten, a DATA step with arrays is an attractive
alternative, and the fact that you can specify the upper bound on the
vector size eliminates the major problem with the array approach. So try:
input IDNUM Type Plan $ Desc $ (StMonth EndMonth)(:monyy6.);
format StMonth EndMonth monyy7.;
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
proc sort data=unflat;
by IDNUM Type StMonth;
array a_Plan (*) $ Plan1-Plan11;
array a_Desc (*) $ Desc1-Desc11;
array a_EndMonth (*) EndMonth1-EndMonth11;
do i = 1 by 1 until (last.StMonth);
by IDNUM Type StMonth;
a_Plan(i) = Plan;
a_Desc(i) = Desc;
a_EndMonth(i) = EndMonth;
drop Plan Desc EndMonth i;
format EndMonth : monyy7.;
On Mon, 20 Jun 2005 08:30:05 -0700, 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 need
>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.