## data representation

Frequent Contributor
Posts: 89

# data representation

Hi All,

I have some doubts in dataset programming,Here i put the dataset skeleton on below.

StatisticsTreatment 1Treatment 2Treatment 3Treatment 4
N2020
Mean3.4000
SD0000
Min-Max0000

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

StatisticsTreatment 1Treatment 2Treatment 3Treatment 4
N2Na2Na
Mean3.4Na0Na
SD0Na0Na
Min-Max0Na0Na

Thanks

Krishna

Super User
Posts: 9,599

## Re: data representation

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;

Super User
Posts: 10,784

## Re: data representation

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

Super Contributor
Posts: 319

## Re: data representation

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;

Posts: 5,535

## Re: data representation

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

PG
Posts: 5,535

## Re: data representation

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

PG
Discussion stats
• 5 replies
• 378 views
• 11 likes
• 5 in conversation