topic Re: inserting zeros for remaining values in SAS Programming

inserting zeros for remaining values

sathya66 — Tue, 19 Jun 2018 09:58:56 GMT

Hi All,

I want to see the values in number column for the first 3 observations for each name and remaining has to be zero for duplicate names in number column.

data test;
infile cards;
input name $ re$ number;
cards;
a store 2
a on    2
a total 2
a store 2
a on    2
a total 2
b store 4
b on    4
b total 4
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
d store 1
d on    1
d total 1
d store 1
d on    1
d total 1
e store 2
e on    2
e total 2
;
run;

/* my output should be like this */

a store 2
a on    2
a total 2
a store 0
a on    0
a total 0
b store 4
b on    4
b total 4
c store 3
c on    3
c total 3
c store 0
c on    0
c total 0
c store 0
c on    0
c total 0
d store 1
d on    1
d total 1
d store 0
d on    0
d total 0
e store 2
e on    2
e total 2

Thanks,

Re: inserting zeros for remaining values

Amir — Tue, 19 Jun 2018 10:19:41 GMT

Hi,

Does the following give you what you want:

data want(drop = counter);
   set test;
   by name;

   if first.name then
      counter = 1;
   else
      counter + 1;

   if counter gt 3 then
      number = 0;
run;

Edit: Assumes the data is sorted as presented.

Regards,

Amir.

Re: inserting zeros for remaining values

rajeshalwayswel — Tue, 19 Jun 2018 10:21:04 GMT

proc sort data=have out=want;
by name res;
run;

data want;
set want;
by name res;
if first.res then number=number;
else number=0;
run;

Re: inserting zeros for remaining values

RW9 — Tue, 19 Jun 2018 10:40:22 GMT

Just to note, the binary choice functions:

data want(drop = counter);
   set test;
   by name;
   counter=ifn(first.name,1,counter+1);
   if counter gt 3 then number=0;
run;

Simplify binary choices.

Re: inserting zeros for remaining values

Ksharp — Tue, 19 Jun 2018 14:04:24 GMT

data test;
infile cards;
input name $ re$ number;
cards;
a store 2
a on    2
a total 2
a store 2
a on    2
a total 2
b store 4
b on    4
b total 4
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
d store 1
d on    1
d total 1
d store 1
d on    1
d total 1
e store 2
e on    2
e total 2
;
run;
data want;
 if _n_=1 then do;
 if 0 then set test;
 declare hash h(dataset:'test');
 h.definekey('name','re');
 h.definedone();
 end;
set test;
if h.check()=0 then h.remove();
 else number=0;
run;
proc print;run;

Re: inserting zeros for remaining values

novinosrin — Tue, 19 Jun 2018 14:26:42 GMT

data test;
infile cards;
input name $ re$ number;
cards;
a store 2
a on    2
a total 2
a store 2
a on    2
a total 2
b store 4
b on    4
b total 4
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
c store 3
c on    3
c total 3
d store 1
d on    1
d total 1
d store 1
d on    1
d total 1
e store 2
e on    2
e total 2
;
run;

data want;
do _n_=1 by 1 until(last.name);
set test;
by name;
if _n_>3 then number=0;
output;
end;
run;