DATA Step, Macro, Functions and more

inserting zeros for remaining values

Reply
Frequent Contributor
Posts: 76

inserting zeros for remaining values

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,

 

SS

Super Contributor
Posts: 339

Re: inserting zeros for remaining values

[ Edited ]

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.

 

 

 

 

 

Super User
Super User
Posts: 9,599

Re: inserting zeros for remaining values

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. 

Frequent Contributor
Posts: 86

Re: inserting zeros for remaining values

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;
Super User
Posts: 10,766

Re: inserting zeros for remaining values

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;
PROC Star
Posts: 1,772

Re: inserting zeros for remaining values

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;
Ask a Question
Discussion stats
  • 5 replies
  • 89 views
  • 5 likes
  • 6 in conversation