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
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.
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.
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.