BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Zatere
Quartz | Level 8

Hi there,

 

I wondered if you could help me.

 

I have the below dataset:

 

data have;
input id date :ddmmyy10.;
format date ddmmyy10.;
datalines;
1 20/11/2019
1 21/11/2019
1 22/11/2019
1 25/11/2019
1 27/11/2019
1 29/11/2019
2 20/11/2019
2 21/11/2019
2 23/11/2019
2 25/11/2019
2 26/11/2019
2 28/11/2019
3 29/11/2019
3 01/12/2019
;
run;

I would like to add a column that represents the number of dates in a row.

This is what I wanted:

 

data want;
input id date :ddmmyy10. COLUMN_NEEDED;
format date ddmmyy10.;
datalines;
1 20/11/2019 1
1 21/11/2019 2
1 22/11/2019 3
1 25/11/2019 1
1 27/11/2019 1
1 29/11/2019 1
2 20/11/2019 1
2 21/11/2019 2
2 23/11/2019 1
2 25/11/2019 1
2 26/11/2019 2
2 28/11/2019 1
3 29/11/2019 1
3 01/12/2019 1
;
run;

As long as there are dates in a row by each ID, the COLUMN_NEEDED adds 1.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
sbxkoenk
SAS Super FREQ

Like this?

data want(drop=a);
 set have;
 retain column_needed;
 by id;
 a=lag(date);
 if first.id then column_needed=1;
 else do;
  if INTCK('day',a,date)=1 then column_needed=column_needed+1;
  else column_needed=1;
      end;
run;
/* end of program */

Koen

View solution in original post

2 REPLIES 2
sbxkoenk
SAS Super FREQ

Like this?

data want(drop=a);
 set have;
 retain column_needed;
 by id;
 a=lag(date);
 if first.id then column_needed=1;
 else do;
  if INTCK('day',a,date)=1 then column_needed=column_needed+1;
  else column_needed=1;
      end;
run;
/* end of program */

Koen

Amir
PROC Star

Hi,

 

Thank you for supplying the data in the form of data steps.

 

I added a couple of extra rows to your have data set for testing:

 

/* added 2 extra rows at end to test consecutive dates */
/* that spread over different id values are handled OK */
data have;
input id date :ddmmyy10.;
format date ddmmyy10.;
datalines;
1 20/11/2019
1 21/11/2019
1 22/11/2019
1 25/11/2019
1 27/11/2019
1 29/11/2019
2 20/11/2019
2 21/11/2019
2 23/11/2019
2 25/11/2019
2 26/11/2019
2 28/11/2019
3 29/11/2019
3 01/12/2019
3 02/12/2019
4 03/12/2019
;

/* use DOW loop to process by id */
data want2(drop = last_date);
  do until (last.id);
    set have;
    
    by id;
    
    if sum(date, -last_date) = 1 then
      column_needed + 1;
    else
      column_needed = 1;
      
    last_date = date;
    output;
  end;
run;

 

The above gives the following output:

 

Amir_0-1702924954005.png

 

 

Thanks & kind regards,

Amir.

sas-innovate-2024.png

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.

 

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
  • 2 replies
  • 308 views
  • 4 likes
  • 3 in conversation