topic Re: How to transpose data to get desired output data in SAS Programming

How to transpose data to get desired output data

knveraraju91 — Wed, 15 Feb 2017 07:01:21 GMT

Dear,

I need to transpose data one by phase, aebod,aede to create a table.

My code works fine when there is no phase variable. Please suggest any option in my code to eliminate several data steps

data one;
input phase $ aebod $ aede $ trt $ ns;
datalines;
s c b 15 1
s c p 30 1
t c p 15 3
t c p 30 3
t c p 17 3
;

output needed:

The dose values are prefixed with phase

aebod aede t17 t15 t30 tT s17 s15 s30 sT
c b 0 0 0 0 0 1 0 1
c p 3 3 3 9 0 1 1 2

proc transpose data=NSWTEAEAEDECODD out=all(drop=_NAME_);
by aebod aede;
var NS;
id TRT;
run;
data all1;
set all;
array data_17_mg _15_mg _30_mg;
do over data;
if missing(data) then data=0;
end;
TOTAL=_17_mg + _15_mg + _30_mg ;
run;

Re: How to transpose data to get desired output data

Ksharp — Wed, 15 Feb 2017 10:26:18 GMT

 
data one;
input phase $ aebod $ aede $ trt $ ns;
datalines;
s c b 15 1
s c b 30 1
t c p 15 3
t c p 30 3
t c p 17 3
;
proc transpose data=one out=all(drop=_NAME_);
by aebod aede;
var NS;
id phase TRT;
run;
proc stdize data=all out=temp reponly missing=0;
run;
data want;
 set temp;
 _total_s=sum(of s:);
 _total_t=sum(of t:);
run;

Re: How to transpose data to get desired output data

rogerjdeangelis — Wed, 15 Feb 2017 17:27:51 GMT

HAVE

Obs   AEBOD PHASE  TRT   AEDE     NS

  1     c     s    15     b        1
  2     c     s    30     p        1
  3     c     t    15     p        3
  4     c     t    30     p        3
  5     c     t    17     p        3
  6     d     s    15     b        1
  7     d     s    30     p        1
  8     d     t    15     p        3
  9     d     t    30     p        3
 10     d     t    17     p        3

WANT

Obs    MJR    S15   S17   S30    T15    T17    T30   SUM

 1     c       1     0     1      3      3      3     11
 2     d       1     0     1      3      3      3     11

 3     Sum     2     0     2      6      6      6     22

WORKING CODE
============

   *get missing levels by way of sparse;
   proc freq data=have;
   table aebod*phase*trt/list sparse out=have1(where=(count=0));
   run;quit;

FULL SOLUTION
=============

* create some data;
data have;
length mjr mnr $3;
input phase $ aebod $ aede $ trt $ ns;
cards4;
s c b 15 1
s c p 30 1
t c p 15 3
t c p 30 3
t c p 17 3
s d b 15 1
s d p 30 1
t d p 15 3
t d p 30 3
t d p 17 3
;;;;
run;quit;


*get missing class;
proc freq data=have;
table aebod*phase*trt/list sparse out=have1(where=(count=0));
run;quit;

/*
* note counts of 0;
Up to 40 obs WORK.HAVE1 total obs=2

Obs    AEBOD  PHASE  TRT COUNT

 1     c      s      17      0
 2     d      s      17      0
*/

* define major and minor;
data have2;
  set have(in=hav) have1(in=hav1);
  if hav1 then do;
     ns=0;
  end;
  mjr= aebod ;
  mnr=cats(phase,put(trt,2.));
  output;
  keep mjr mnr ns;
run;quit;

/*
Up to 40 obs WORK.HAVE2 total obs=12

Obs    MJR    MNR              NS

  1    c      s15               1
  2    c      s30               1
  3    c      t15               3
  4    c      t30               3
  5    c      t17               3
  6    d      s15               1
...
*/

Ods Exclude All;
Ods Output Observed=have3(rename=label=Mjr);
Proc Corresp Data=have2 Observed dim=1;
   Table mjr,  mnr;
   weight ns;
Run;quit;
Ods Select All;


/*
Up to 40 obs from have3 total obs=3

Obs    MJR    S15    S30    T15    T17    T30    SUM

 1     c       1      1      3      3      3     11
 2     d       1      1      3      3      3     11
 3     Sum     2      2      6      6      6     22
*/

proc transpose data=have2(where=(ns=0)) out=havxpo(drop=_name_);
  by mjr;
  id mnr;
  var ns;
run;quit;

/*
Up to 40 obs WORK.HAVXPO total obs=2

Obs    MJR  S17

 1     c      0
 2     d      0
*/

* add missing levels;
data want;
  merge have3 havxpo(in=xpo);
run;quit;

options missing='0';
proc print data=have3 width=min;
run;quit;


Obs    MJR    S15   S17   S30    T15    T17    T30   SUM

 1     c       1     0     1      3      3      3     11
 2     d       1     0     1      3      3      3     11

 3     Sum     2     0     2      6      6      6     22

Re: How to transpose data to get desired output data

art297 — Wed, 15 Feb 2017 18:19:02 GMT

While I think that @Ksharp has already provided the code that answers your question, if including the values that weren't present in your data (i.e. s17) and/or the order of your variables is important, the following slight (?) modification of his code provides both.

It uses the transpose macro (http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset ) that he, @Astounding, Joe Whitehurst and I wrote a couple of years ago. You would first have to download and run that macro.

data one;
  input phase $ aebod $ aede $ trt $ ns;
  datalines;
s c b 15 1
s c b 30 1
t c p 15 3
t c p 30 3
t c p 17 3
;

data onea;
  set one;
  phase_trt=catt(phase,trt);
run;

data order;
  informat phase_trt $5.;
  format phase_trt $5.;
  input phase_trt order;
  cards;
t17 1
t15 2
t30 3
_tT 4
s17 5
s15 6
s30 7
_sT 8
;

%transpose(data=one, out=all, by=aebod aede, id=phase_trt, guessingrows=1000,
 var=NS, use_varname=no, preloadfmt=order)

proc stdize data=all out=temp reponly missing=0;
run;

data want;
  set temp;
  _tT=sum(of s:);
  _sT=sum(of t:);
run;

HTH,

Art, CEO, AnalystFinder.com