BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michellel
Calcite | Level 5

Hi,

I have a question about how to count within groups. I have a data set like below and I want to keep the records only on the first same date for each person. I am trying to create new variable [count] like the second one in below, so that I could keep only count=1 to get what I need. I tried different ways, but always got result like variable [count_wrong]. Could you give me some hints? Thank so much!!

id     date

1     1/1/2011

1     1/1/2011

1     2/3/2011

2     2/2/2011

2     3/5/2012

2     3/5/2012

2     4/8/2012

3     2/5/2011

3     2/5/2011

3     3/8/2011

3     4/9/2011

id     date          count     count_wrong

1     1/1/2011     1                    1

1     1/1/2011     1                    2

1     2/3/2011     2                    1

2     2/2/2011     1                    1

2     3/5/2012     2                    1

2     3/5/2012     2                    2

2     4/8/2012     3                    1

3     2/5/2011     1                    1

3     2/5/2011     1                    2

3     3/8/2011     2                    1

3     4/9/2011     3                    1

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

data want(drop=count);

set have;

by id date;

if first.id then count=0;

if first.date then count+1;

if count=1;

run;

View solution in original post

8 REPLIES 8
Steelers_In_DC
Barite | Level 11

if a count is necessary give me an example of the output that you want, otherwise I think this is what you are looking for:

data have;

infile cards dsd;

informat date mmddyy10.;

format date mmddyy10.;

input id $ date;

cards;

1,1/1/2011

1,1/1/2011

1,2/3/2011

2,2/2/2011

2,3/5/2012

2,3/5/2012

2,4/8/2012

3,2/5/2011

3,2/5/2011

3,3/8/2011

3,4/9/2011

;

run;

proc sort data=have;by id date;

data want(drop=ldate) check;

set have;

by id;

ldate = lag(date);

output check;

if not first.id and ldate = date then output want;

run;

michellel
Calcite | Level 5

Thanks Mark. Here is the final data I want. Actually I do not need the variable [count]. I just wanted to find a way to get the data I need. Thanks.

id     date          count    

1     1/1/2011     1                   

1     1/1/2011     1                   

2     2/2/2011     1                   

3     2/5/2011     1                   

3     2/5/2011     1                   

Steelers_In_DC
Barite | Level 11

why

2 2/2/2011 1

?

michellel
Calcite | Level 5

Actually I do not need the variable [count]. I just want to keep all rows of record on the first date for each person. Thanks.

michellel
Calcite | Level 5

I ran your program above, but did not get the result I want. Thanks anyway.

stat_sas
Ammonite | Level 13

data want(drop=count);

set have;

by id date;

if first.id then count=0;

if first.date then count+1;

if count=1;

run;

Ksharp
Super User
data have;
infile cards dsd;
informat date mmddyy10.;
format date mmddyy10.;
input id $ date;
cards;
1,1/1/2011
1,1/1/2011
1,2/3/2011
2,2/2/2011
2,3/5/2012
2,3/5/2012
2,4/8/2012
3,2/5/2011
3,2/5/2011
3,3/8/2011
3,4/9/2011
;
run;
data want;
 set have;
 by id  date;
 if first.id then count=0;
 count+first.date;
 if count=1;
run;

Xia Keshan

AnnaShu
Calcite | Level 5

data have;

infile cards dsd;

informat date mmddyy10.;

format date mmddyy10.;

input id $ date;

cards;

1,1/1/2011

1,1/1/2011

1,2/3/2011

2,2/2/2011

2,3/5/2012

2,3/5/2012

2,4/8/2012

3,2/5/2011

3,2/5/2011

3,3/8/2011

3,4/9/2011

;

run;

data want;

set have;

by id date;

if first.id then count=0;

if first.date then count+1;

retain count;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3028 views
  • 0 likes
  • 5 in conversation