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;
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;
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.