How to create identifiers for Successive Values at different time intervals

Donnie — Wed, 19 May 2021 14:17:51 GMT

Dear SAS Programmers, I need help to write a code that creates indentification for nonsuccessive values. Here is a sample of my dataset:

Date time ex_value op_type TYPE matched

01/01/2020 8:30 20 C Q 1

01/01/2020 8:32 20 C Q 1

01/01/2020 8:34 65 C T 8

01/01/2020 8:36 65 C T 12

01/01/2020 8:39 72 C T 10

01/01/2020 8:59 72 P Q 1

01/01/2020 9:09 72 P Q 1

01/01/2020 10:09 72 P Q 1

01/01/2020 10:19 72 P Q 1

01/01/2020 10:29 72 P T 58

01/01/2020 10:29 72 P T 35

* My identifiers are date, ex_value and op_type.

* Using the TYPE variable as an identifier will mess up the time chronology (or the time variable)

* Now I want to create an identifier for the matched variable (Let's call this new identifier variable Matched_ID) with successive values of 1 (also identified by TYPE = "Q") so that the data will look like:

Date time ex_value op_type TYPE matched Matched_ID

01/01/2020 8:30 20 C Q 1 1

01/01/2020 8:32 20 C Q 1 1

01/01/2020 8:34 65 C T 8 .

01/01/2020 8:36 65 C T 12 .

01/01/2020 8:39 72 C T 10 .

01/01/2020 8:59 72 P Q 1 2

01/01/2020 9:09 72 P Q 1 2

01/01/2020 10:09 72 P Q 1 2

01/01/2020 10:19 72 P Q 1 2

01/01/2020 10:29 72 P T 58 .

01/01/2020 10:29 72 P T 35 .

Thanks all.

Re: How to create identifiers for Successive Values at different time intervals

jimbarbour — Tue, 25 May 2021 07:48:13 GMT

@Donnie,

I think you want your results to looks like the below, yes?

If so, the below code should do the trick. It's late, and I banged this out fairly quickly, so be sure to test it before you rely on its results fully with larger sets of data.

Jim

DATA	WORK.Pre_Match_Data;
	DROP	_:;

	LENGTH	Date  		$10.
			time  		$5.
			ex_value	$2.
			op_type  	$1.
			TYPE		$1.
			matched		$2.
			;

	INFILE	DATALINES4;
	INPUT	Date  		$
			time  		$
			ex_value	$
			op_type  	$
			TYPE		$
			matched		$
			;

DATALINES4;
01/01/2020          8:30               20                 C        Q         1
01/01/2020          8:32               20                 C        Q         1
01/01/2020          8:34               65                 C         T         8
01/01/2020          8:36               65                 C         T        12
01/01/2020          8:39               72                 C         T        10
01/01/2020          8:59               72                 P         Q        1
01/01/2020          9:09               72                 P         Q        1
01/01/2020          10:09             72                 P          Q       1
01/01/2020          10:19             72                 P          Q       1
01/01/2020          10:29             72                 P          T       58
01/01/2020          10:29             72                 P          T       35
;;;;
RUN;

**------------------------------------------------------------------------------**;

DATA	WORK.Matching_Data;
	DROP	_:;

	LENGTH	_Date  		$10.
			_ex_value	$2.
			_op_type  	$1.
			;

	RETAIN	_Date
			_ex_value
			_op_type
			;

	RETAIN	_First_Time	1;

	SET	WORK.Pre_Match_Data;

	RETAIN	Matched_ID	.;
	RETAIN	_Tracker	1;

	IF	_First_Time							THEN
		DO;
			_First_Time		=	0;
			IF	Type		=	'Q'			THEN
				Matched_ID	=	1;
			ELSE
				Matched_ID	=	.;
		END;
	ELSE
	IF	Date				=	_Date		AND
		ex_value			=	_ex_Value	AND
		op_type				=	_op_type	THEN
		IF	Type			=	'Q'			THEN
			DO;
				Matched_ID	=	_Tracker;
			END;
		ELSE
			DO;
				Matched_ID	=	.;
			END;
	ELSE
		IF	Type			=	'Q'			THEN
			DO;
				_Tracker	+	1;
				Matched_ID	=	_Tracker;
			END;
		ELSE
			DO;
				Matched_ID	=	.;
			END;

	_Date					=	Date;
	_ex_value				=	ex_Value;
	_op_type				=	op_type;
RUN;

topic How to create identifiers for Successive Values at different time intervals in SAS Programming

How to create identifiers for Successive Values at different time intervals

Re: How to create identifiers for Successive Values at different time intervals