BookmarkSubscribeRSS Feed
KrishnaChandra
Calcite | Level 5

  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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Barite | Level 11

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
Opal | Level 21

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
PGStats
Opal | Level 21

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1207 views
  • 11 likes
  • 5 in conversation