How to reduce number of rows in the proc transpose output

[knveraraju91]

Dear,

I need help in my proc transpose procedure to get output. I am not getting the output I need. Please help in my code.

data one;
input trt $ aebod $ ns;
datalines;
a ca 4
b ca 6
e ca 2
d ca 4
c ge 4
e ge 7
a ge 6
;
proc transpose data=one out=two(drop=_NAME_);
by aebod notsorted;
var NS;
id TRT;
run;

data three;
set two;
array data e a b c d;
do over data;
if missing(data) then data=0;
end;
TOTAL=a+ b+ c+ d;
run;

proc sort data=three;
by aebod;
run;

output getting:

aebod a b c d e Total
ca      4 0 0 0 0 4
ca      0 6 0 0 0 6
ca      0 0 0 4 0 0
ca      0 0 0 0 2 0
ge      6 0 0 0 0 6
ge      0 0 0 0 7 0

output needed:

aebod a b c d e Total
ca      4 6 0 4 2 14
ge      6 0 0 0 7 6

[art297]

Assuming that your example output for ge was missing one entry, the following would do what you want:

data one;
  input trt $ aebod $ ns;
  datalines;
a ca 4
b ca 6
e ca 2
d ca 4
c ge 4
e ge 7
a ge 6
;

proc sort data=one;
  by aebod trt;
run;

proc transpose data=one out=two(drop=_NAME_);
  by aebod;
  var NS;
  id TRT;
run;

data two;
  retain aebod a b c d e;
  array todo a b c d e;
  set two;
  total=sum(a,b,c,d);
  do over todo;
    if missing(todo) then todo=0;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

[knveraraju91]

Thank you for the support. I should have sorted the dataset before transposing. After sorting I got the output needed with my code. Thank you for your code.

Thank you

[Reeza]

Your proc tranpsose is incorrect, you need to sort it first if you want that output. 

Remove the NOTSORTED option and presort your data.