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

Hi,

 

I have some data below that i generated using the following code

 

data new2;
set new;

if id^=lag(id) or v1^=lag(v1) then time+1;
else time+0;

run;

 

What i am trying to do is have the time variable reset to "1" once the ID variable changes (observation 21 - time "5" ) and for the rest of this dataset where the ID changes. Up until Obs 20 the time variable is correct.

 

Thank you

 

SAS Output

SAS Output
Obs v1 v2 v3 v4 time 
201306101118231950.82783113.11
201306101118231950.76539713.11
201306101118231950.75580913.11
201306101118231950.57285513.11
201306101118231950.73443113.11
201306101118232170.84595212.72
201306101118232170.7917612.72
201306101118232170.76739812.72
201306101118232170.74525512.72
201306101118232170.9221812.72
201306101118232230.74725913.23
201306101118232230.72281313.23
201306101118232230.73311113.23
201306101118232230.79569213.23
201306101118232230.86348113.23
201306101118232600.75983912.64
201306101118232600.7237312.64
201306101118232600.7491212.64
201306101118232600.7150912.64
201306101118232600.81642412.64
201306120923292540.59326311.15
201306120923292540.59249511.15
201306120923292570.5782497.96
201306120923292570.9806447.96
201306120923292580.818048117
201306120923292580.885162117
201306120923292580.793767117
201306120923292580.36211111.77
201306120923292580.53945311.77
201306120923292580.65954111.97
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisWard
SAS Employee

Assuming the data is sorted by id the following code works.

 

data new;  * read test data ;
  input id $14. v1 v2 v3 old_time;
cards;
20130610111823	195	0.827831	13.1	1
20130610111823	195	0.765397	13.1	1
20130610111823	195	0.755809	13.1	1
20130610111823	195	0.572855	13.1	1
20130610111823	195	0.734431	13.1	1
20130610111823	217	0.845952	12.7	2
20130610111823	217	0.79176	12.7	2
20130610111823	217	0.767398	12.7	2
20130610111823	217	0.745255	12.7	2
20130610111823	217	0.92218	12.7	2
20130610111823	223	0.747259	13.2	3
20130610111823	223	0.722813	13.2	3
20130610111823	223	0.733111	13.2	3
20130610111823	223	0.795692	13.2	3
20130610111823	223	0.863481	13.2	3
20130610111823	260	0.759839	12.6	4
20130610111823	260	0.72373	12.6	4
20130610111823	260	0.74912	12.6	4
20130610111823	260	0.71509	12.6	4
20130610111823	260	0.816424	12.6	4
20130612092329	254	0.593263	11.1	5
20130612092329	254	0.592495	11.1	5
20130612092329	257	0.578249	7.9	6
20130612092329	257	0.980644	7.9	6
20130612092329	258	0.818048	11	7
20130612092329	258	0.885162	11	7
20130612092329	258	0.793767	11	7
20130612092329	258	0.362111	11.7	7
20130612092329	258	0.539453	11.7	7
20130612092329	258	0.659541	11.9	7
;;;
run
;;;

data new2;  * create new data ;
set new;
by id;
if first.id then time=0;
if id^=lag(id) or v1^=lag(v1) then time+1;
run;

View solution in original post

8 REPLIES 8
Reeza
Super User
How do you know when an ID changes?
SasResearch
Fluorite | Level 6

Hi, thank you for the reply.

 

The ID variable is the first variable on the far left. 

 

20130610111823 is the first ID up until the 20th observation. 21 is the new ID

data_null__
Jade | Level 19

Why don't you just

SET...

BY ID TIME NOSTORTED;

 

The you can use first and last variables.

SasResearch
Fluorite | Level 6

The time variable is the one that I am creating, thats where my issue is. I am trying to get it to begin again at time 1 for each unique ID.

data_null__
Jade | Level 19

OK then V1.

ChrisWard
SAS Employee

Assuming the data is sorted by id the following code works.

 

data new;  * read test data ;
  input id $14. v1 v2 v3 old_time;
cards;
20130610111823	195	0.827831	13.1	1
20130610111823	195	0.765397	13.1	1
20130610111823	195	0.755809	13.1	1
20130610111823	195	0.572855	13.1	1
20130610111823	195	0.734431	13.1	1
20130610111823	217	0.845952	12.7	2
20130610111823	217	0.79176	12.7	2
20130610111823	217	0.767398	12.7	2
20130610111823	217	0.745255	12.7	2
20130610111823	217	0.92218	12.7	2
20130610111823	223	0.747259	13.2	3
20130610111823	223	0.722813	13.2	3
20130610111823	223	0.733111	13.2	3
20130610111823	223	0.795692	13.2	3
20130610111823	223	0.863481	13.2	3
20130610111823	260	0.759839	12.6	4
20130610111823	260	0.72373	12.6	4
20130610111823	260	0.74912	12.6	4
20130610111823	260	0.71509	12.6	4
20130610111823	260	0.816424	12.6	4
20130612092329	254	0.593263	11.1	5
20130612092329	254	0.592495	11.1	5
20130612092329	257	0.578249	7.9	6
20130612092329	257	0.980644	7.9	6
20130612092329	258	0.818048	11	7
20130612092329	258	0.885162	11	7
20130612092329	258	0.793767	11	7
20130612092329	258	0.362111	11.7	7
20130612092329	258	0.539453	11.7	7
20130612092329	258	0.659541	11.9	7
;;;
run
;;;

data new2;  * create new data ;
set new;
by id;
if first.id then time=0;
if id^=lag(id) or v1^=lag(v1) then time+1;
run;
ChrisWard
SAS Employee

If the IDs aren't sorted, but are grouped together this would also work.

 

data new2;
  set new;
  if id^=lag(id) then time=0;
  if v1^=lag(v1) then time+1;
run;
SasResearch
Fluorite | Level 6

Thank you! this works. 

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
  • 1633 views
  • 0 likes
  • 4 in conversation