topic Re: How to reduce number of rows in the proc transpose output in SAS Programming

How to reduce number of rows in the proc transpose output

knveraraju91 — Sat, 11 Feb 2017 01:53:02 GMT

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

Re: How to reduce number of rows in the proc transpose output

art297 — Sat, 11 Feb 2017 02:24:04 GMT

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

Re: How to reduce number of rows in the proc transpose output

Reeza — Sat, 11 Feb 2017 02:24:28 GMT

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

Remove the NOTSORTED option and presort your data.

Re: How to reduce number of rows in the proc transpose output

knveraraju91 — Sat, 11 Feb 2017 02:50:30 GMT

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