## inserting zeros for remaining values

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