topic Transpose in SAS Programming

Transpose

sascodequestion — Sun, 05 Feb 2017 22:08:05 GMT

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
;

Re: Transpose

mkeintz — Sun, 05 Feb 2017 22:23:20 GMT

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

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

Please try to avoid mindreading requirements.

Re: Transpose

sascodequestion — Sun, 05 Feb 2017 22:40:25 GMT

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

Re: Transpose

art297 — Sun, 05 Feb 2017 23:10:55 GMT

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

Re: Transpose

mkeintz — Mon, 06 Feb 2017 05:55:32 GMT

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.