Transpose

[sascodequestion]

Hi Friends,

I have a data set like below (input) and need your help in getting the output dataset.

data input;
input ColumnA $ ColumnB $ ColumnC $ ColumnD$ Value;
cards;
A B C A1 1
A B C A1 2
A B C A1 3
A B C A1 4
A B C A2 1
A B C A2 2
A B C A2 3
A B C A2 4
;

output:

data output;
input ColumnA $ ColumnB $ ColumnC $ A1 A2;
cards;
A B C 1 1
A B C 2 2
A B C 3 3
A B C 4 4
;

[mkeintz]

YOu have not sufficiently described the rules by which you want to associate the data records.

1. Are you associating records within columna/columnb/columnc groups?
2. Are you pairing columnd='A1' and columnd='A2' records?
3. If so, are they associated by position within a/b/c?  
4. Do you always expect the same number of A1's and A2's?

Please try to avoid mindreading requirements.

[sascodequestion]

Hi,

Thanks for your response.

1.The total number of observations should be devided into 2 groups, which is based on the values of the coulmnD(A1 and A2)

2. The observations  should be grouped based on ColumnA,columnB, ColumnC and columnD

3. The Values in the columnD should become new coulmns, for example in the above example we have two different values A1 and A2 so we should get 2 new columns in the output called A1 and A2

3. Column A1 and A2  no need to have same rows. The total number of observation are going to be equal to the  higher number of observations in any group.  For example if group A1 contains 5 and A2 contains 3, so total number of obs will be equal to 5 and column A2 will have values 0.

Hope this is clear

[art297]

You would have to create new variables to account for what you want. Given your example you could use something like:

data input;
input ColumnA $ ColumnB $ ColumnC $ ColumnD $ Value;
cards;
A B C A1 1
A B C A1 2
A B C A1 3
A B C A1 4
A B C A2 1
A B C A2 2
A B C A2 3
A B C A2 4
;

data need;
  set input;
  if mod(_n_,4) eq 0 then x=4;
  else x=mod(_n_,4);
run;

proc sort data=need;
  by ColumnA ColumnB ColumnC x ColumnD;
run;

data need;
  set need;
  if mod(_n_,2) eq 1 then group+1;
run;

proc transpose data=need out=want (drop= group _name_);
  by ColumnA ColumnB ColumnC Group;
  var value;
  id ColumnD;
run;

HTH,

Art, CEO, AnalystFinder.com

[mkeintz]

Assuming (1) your data are sorted by columna/columnb/columnc and (2) columnd='A1' or 'A2', then you can use a MERGE process as below:

data want (drop=n n1 n2 columnd);
  n1=0; n2=0;
  do until (last.columnc);
    set input (keep=columna columnb columnc columnd);
    by columna columnb columnc;
    if      columnd='A1' then n1=n1+1;
    else if columnd='A2' then n2=n2+1;
  end;

  do N=1 by 1 until (last.columnc);
    merge input (in=in1 where=(columnd='A1') rename=(value=a1))
          input (in=in2 where=(columnd='A2') rename=(value=a2));
    by columna columnb columnc;
    if n>n1 then a1=0;
    if n>n2 then a2=0;
    output;
  end;
run;

Note that N1 and N2 are the counts of A1 and A2 records respectively.