data representation

[KrishnaChandra]

  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

[RW9]

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;

[Ksharp]

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

[Loko]

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;

[PGStats]

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

[PGStats]

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