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.
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.