BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;

3 REPLIES 3
Ksharp
Super User
 
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;
rogerjdeangelis
Barite | Level 11
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



art297
Opal | Level 21

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 773 views
  • 6 likes
  • 4 in conversation