Hi All,
I have some doubts in dataset programming,Here i put the dataset skeleton on below.
Statistics | Treatment 1 | Treatment 2 | Treatment 3 | Treatment 4 |
---|---|---|---|---|
N | 2 | 0 | 2 | 0 |
Mean | 3.4 | 0 | 0 | 0 |
SD | 0 | 0 | 0 | 0 |
Min-Max | 0 | 0 | 0 | 0 |
1) I would like to put 'NA' on each columns which doesn't have any values(Treatment 2 and Treatment 4) .
2) second thing is to retain the '0' on each columns which has a single row values(Treatment 1 and Treatment 3).
My expected Output is shown in below
Statistics | Treatment 1 | Treatment 2 | Treatment 3 | Treatment 4 |
---|---|---|---|---|
N | 2 | Na | 2 | Na |
Mean | 3.4 | Na | 0 | Na |
SD | 0 | Na | 0 | Na |
Min-Max | 0 | Na | 0 | Na |
Thanks
Krishna
Gotta ask, why? I have only ever seen them left 0. Anyways, if you must (note they all need to be character then):
data have;
param="n"; trt1=1; trt2=0; output;
param="mean"; trt1=4; trt2=0; output;
run;
proc sql;
create table WANT as
select PARAM,
case when (select sum(TRT1) from HAVE)=0 then "Na"
else strip(put(TRT1,best.)) end as TRT1,
case when (select sum(TRT2) from HAVE)=0 then "Na"
else strip(put(TRT2,best.)) end as TRT2
from HAVE;
quit;
Assuming these zero are actually missing value.
data have; input Statistics $ Treatment1 Treatment2 Treatment3 Treatment4 ; cards; N 2 . 2 . Mean 3.4 . . . SD . . . . Min-Max . . . . ; run; proc format; value fmt .='NA'; run; proc stdize data=have out=want reponly missing=0; var Treatment1 Treatment3; format Treatment2 Treatment4 fmt.; run;
Xia Keshan
hello,
just to show a different approach:
data have;
input statistics $ Treatment1 Treatment2 Treatment3 Treatment4 ;
datalines;
Mean 3.4 0 2 0
Min-Max 0 0 0 0
N 2 0 0 0
SD 0 0 0 0
;
proc means data=have;
output out=r (drop=_type_ _Freq_) sum= / autoname;
run;
%let nvar=%sysfunc(attrn(%sysfunc(open(r,i)),nvars));
%put &nvar;
data want;
retain Treatment:;
set have;
if _n_=1 then set r;
array treat{*} Treatment: ;
array newtreat{&nvar} $ newtreat1-newtreat4;
do i=1 to &nvar;
if treat{&nvar+i}=0 then newtreat{i}='Na';
else newtreat{i}=left(treat{i});
end;
drop i Treatment:;
run;
Use the fact that if N=0 then the column is all zero. Set those columns to missing and assign a format to the missing values.
data have;
input Statistics $ Treatment1 Treatment2 Treatment3 Treatment4;
datalines;
N 2 0 2 0
Mean 3.4 0 0 0
SD 0 0 0 0
Min-Max 0 0 0 0
;
proc format;
value zeroNa
. = "Na";
run;
data want;
set have;
array t treatment:;
array n{1000} _temporary_;
if statistics="N" then
do i = 1 to dim(t);
n{i} = t{i} = 0;
end;
do i = 1 to dim(t);
if n{i} then call missing(t{i});
end;
drop i;
format treatment: zeroNa.;
run;
proc print data=want noobs; run;
PG
Actually, it might be better to leave the explicit N=0 in the table and only set the other statistics to missing:
data have;
input Statistics $ Treatment1 Treatment2 Treatment3 Treatment4;
datalines;
N 2 0 2 0
Mean 3.4 0 0 0
SD 0 0 0 0
Min-Max 0 0 0 0
;
proc format;
value zeroNa
. = "Na";
run;
data want;
set have;
array t treatment:;
array n{1000} _temporary_;
if statistics="N" then
do i = 1 to dim(t);
n{i} = t{i} = 0;
end;
else do i = 1 to dim(t);
if n{i} then call missing(t{i});
end;
drop i;
format treatment: zeroNa.;
run;
proc print data=want noobs; run;
PG
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.