topic data representation in SAS Programming

data representation

KrishnaChandra — Fri, 09 Jan 2015 08:32:27 GMT

Hi All,

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

Statistics	Treatment 1	Treatment 3
N	2	2
Mean	3.4	0
SD	0	0
Min-Max	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

Re: data representation

RW9 — Fri, 09 Jan 2015 09:28:36 GMT

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;

Re: data representation

Ksharp — Fri, 09 Jan 2015 10:32:53 GMT

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

Re: data representation

Loko — Fri, 09 Jan 2015 12:31:23 GMT

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;

Re: data representation

PGStats — Sat, 10 Jan 2015 03:58:30 GMT

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;

Re: data representation

PGStats — Sat, 10 Jan 2015 05:03:22 GMT

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;