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
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.
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.