I have a dataset below:
date id first
MAY 1 1
MAY 1
JUN 1
SEP 2 1
SEP 2
I want record with same date to mark as first=1, like
date id first
MAY 1 1
MAY 1 1
JUN 1
SEP 2 1
SEP 2 1
group by id date.
cannot use retain, cause it failed as
date id first
MAY 1 1
MAY 1 1
JUN 1 1 -----------I don't want JUN to be as first=1, I just want all same date MAY to be first=1.
What about September?
You're correct that RETAIN may not work - and it likely own't with a variable already present. But if you create a new variable and work with that it will work fine.
data have;
input date :$3. id first;
cards;
MAY 1 1
MAY 1 .
JUN 1 .
SEP 2 1
SEP 2 .
;
data temp;
set have;
by date notsorted;
retain flag;
if first.date and first=1 then
flag=1;
else if first.date then
call missing(flag);
run;
data want;
set temp;
where flag=1;
run;
@jojozheng wrote:
I have a dataset below:
date id first
MAY 1 1
MAY 1
JUN 1
SEP 2 1
SEP 2
I want record with same date to mark as first=1, like
date id first
MAY 1 1
MAY 1 1
JUN 1
SEP 2 1
SEP 2 1
group by id date.
cannot use retain, cause it failed as
date id first
MAY 1 1
MAY 1 1
JUN 1 1 -----------I don't want JUN to be as first=1, I just want all same date MAY to be first=1.
Why did you start a new thread?
What's wrong with the one you created here?
Did you try all the solutions given (including @Astounding's?
If you did, explain why they don't suit.
And of course, clearly provide the expected output.
data have;
input date :$3. id first;
cards;
MAY 1 1
MAY 1 .
JUN 1 .
SEP 2 1
SEP 2 .
;
proc sort data=have;
by date;
run;
data want;
do until(last.date);
set have;
by date;
retain cnt;
if first.date then cnt=1;
else cnt+1;
end;
do until(last.date);
set have;
by date;
if cnt>1 then flag=1;
else flag=.;
output;
end;
run;
What about September?
You're correct that RETAIN may not work - and it likely own't with a variable already present. But if you create a new variable and work with that it will work fine.
data have;
input date :$3. id first;
cards;
MAY 1 1
MAY 1 .
JUN 1 .
SEP 2 1
SEP 2 .
;
data temp;
set have;
by date notsorted;
retain flag;
if first.date and first=1 then
flag=1;
else if first.date then
call missing(flag);
run;
data want;
set temp;
where flag=1;
run;
@jojozheng wrote:
I have a dataset below:
date id first
MAY 1 1
MAY 1
JUN 1
SEP 2 1
SEP 2
I want record with same date to mark as first=1, like
date id first
MAY 1 1
MAY 1 1
JUN 1
SEP 2 1
SEP 2 1
group by id date.
cannot use retain, cause it failed as
date id first
MAY 1 1
MAY 1 1
JUN 1 1 -----------I don't want JUN to be as first=1, I just want all same date MAY to be first=1.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.