Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
sagrim
Level II

Create Categorical Variable Using Two Timepoints

If anyone can help with this I would greatly appreciate it !

 

I am trying to create a categorical variable that will reflect changes in employment based on two variables: JOBCODE TimePoint 2 and JOBCODE TimePoint 2.

In essence, this categorical variable will allow me to see whether any given employee has one of the following events from timepoint 1 to timepoint 2: joined the organization (new employee), changed JOBCODE (transferred within the organization), or left the organization (termination/separation).    

The code below represents my data structure is because any new employees will have a blank/ null value for their JOBCODE at Timepoint 1, any terminated employees will have a blank/ null value for JOBCODE at Timepoint 2 and any employees who changed positions will have a non-match JOBCODE in the field from Timepoint 1 to Timepoint 2.

As far as the command for blank/null goes, I have tried a number of different things.  I tried using isMissing as well as == “” and I have also tried !contains.  None of them work.  I don’t have a preference on which one of these styles is used as long as it works. 

For those employees who transfer, I am only interested in whether or not they kept a job code contained in mylist

I created a mock data table.  Column 1 is some made up employee IDs for the sake of example.  Column 2 and 3 represent jobcodes at Timepoint 1 and Timepoint 2.  Column 4 is the output I would expect based on the values I entered in Column 2 & 3.  Column 5 is some notes for clarification. 

 

new column ("JOBCODE Status", Character, Nominal) << Formula

(If (Contains (mylist, :"JOBCODE Timepoint1") & !Contains (mylist, :"JOBCODE Timepoint2"),"Change in JOBCODE",

(If (Contains (mylist, :"JOBCODE Timepoint1") & Contains (mylist, :"JOBCODE Timepoint2"),"No Change",

(If (Contains (mylist, :"JOBCODE Timepoint1") & isMissing (:"JOBCODE Timepoint2"),

"Termination/Separation",

(If (isMissing (mylist, :"JOBCODE Timepoint1") & Contains (:"JOBCODE Timepoint2"),

"New Employee",



))))))));



mylist= {"HH0029",

"HH0030",   

"HH0241",

"HH0242",   

"HH0317",   

"HH0635",   

"HH0635",   

"HH0636",   

"HH0636",   

"HH0639",   

"HH0742",   

"HH0750",   

"HX0002",   

"HX0003",   

"HX0008",   

"HX0010",   

"HX0022",   

"HX0106",   

"HX0106",   

"HX0107",   

"HX0114",   

"HX0115",   

"SH0049",   

"SH0504",   

"SH0678",   

"HH0113",   

"HH0708",   

"HH0746",   

"HX0111",   

"NU0017",   

"NU0100",   

"NU0200",   

"NU0203",   

"NU0206",

"NU0207",

"NU0213",

"NU0243",   

"NU0244",

"NU0300",   

"NU0301",

"NU0305",   

"NU0327",   

"NU0328",   

"NU0362",   

"NU0364",   

"NU0400",   

"NU0422",

"NU0423",

"NU0424",   

"NU0425",   

"NU0440",   

"NU0461",   

"NU0615",   

"NU0618",   

"NU0619",   

"NU0620",   

"NU0621",   

"NU0622",   

"NU0625",   

"NU0800",   

"NU0801",   

"NU0923",   

"NX0002",   

"NX0006",   

"NX0017",   

"NX0018",   

"NX0019",   

"NX0023",   

"NX0125",   

"NX0465",

"NX0466",   

"NX0609",   

"NX0610",   

"NU0610",   

"NU0700",   

"NU0710",   

"NU0713",   

"NU0922",

"NU0364",   

"NX0609",   

"NX0610"};
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Create Categorical Variable Using Two Timepoints

Here is my rework of your code that seems to work

New Column( "myJOBCODE Status", Character, Nominal ) << Formula(
	If( Row() == 1,
		mylist = {"HH0029", "HH0030", "HH0241", "HH0242", "HH0317", "HH0635", "HH0635", "HH0636", "HH0636", "HH0639", "HH0742",
		"HH0750", "HX0002", "HX0003", "HX0008", "HX0010", "HX0022", "HX0106", "HX0106", "HX0107", "HX0114", "HX0115", "SH0049",
		"SH0504", "SH0678", "HH0113", "HH0708", "HH0746", "HX0111", "NU0017", "NU0100", "NU0200", "NU0203", "NU0206", "NU0207",
		"NU0213", "NU0243", "NU0244", "NU0300", "NU0301", "NU0305", "NU0327", "NU0328", "NU0362", "NU0364", "NU0400", "NU0422",
		"NU0423", "NU0424", "NU0425", "NU0440", "NU0461", "NU0615", "NU0618", "NU0619", "NU0620", "NU0621", "NU0622", "NU0625",
		"NU0800", "NU0801", "NU0923", "NX0002", "NX0006", "NX0017", "NX0018", "NX0019", "NX0023", "NX0125", "NX0465", "NX0466",
		"NX0609", "NX0610", "NU0610", "NU0700", "NU0710", "NU0713", "NU0922", "NU0364", "NX0609", "NX0610"}
	);
	isThere = Function( {value},
		{},
		If( Contains( mylist, value ),
			1,
			0
		)
	);
	a = :JOBCODE Timepoint1;
	b = :JOBCODE Timepoint2;
	If(
		isThere( a ) & b == "", "Termination/Separation",
		a == "" & isThere( b ), "New Employee",
		isThere( a ) & isThere( b ), "No Change"
	);
);
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Create Categorical Variable Using Two Timepoints

Here is my rework of your code that seems to work

New Column( "myJOBCODE Status", Character, Nominal ) << Formula(
	If( Row() == 1,
		mylist = {"HH0029", "HH0030", "HH0241", "HH0242", "HH0317", "HH0635", "HH0635", "HH0636", "HH0636", "HH0639", "HH0742",
		"HH0750", "HX0002", "HX0003", "HX0008", "HX0010", "HX0022", "HX0106", "HX0106", "HX0107", "HX0114", "HX0115", "SH0049",
		"SH0504", "SH0678", "HH0113", "HH0708", "HH0746", "HX0111", "NU0017", "NU0100", "NU0200", "NU0203", "NU0206", "NU0207",
		"NU0213", "NU0243", "NU0244", "NU0300", "NU0301", "NU0305", "NU0327", "NU0328", "NU0362", "NU0364", "NU0400", "NU0422",
		"NU0423", "NU0424", "NU0425", "NU0440", "NU0461", "NU0615", "NU0618", "NU0619", "NU0620", "NU0621", "NU0622", "NU0625",
		"NU0800", "NU0801", "NU0923", "NX0002", "NX0006", "NX0017", "NX0018", "NX0019", "NX0023", "NX0125", "NX0465", "NX0466",
		"NX0609", "NX0610", "NU0610", "NU0700", "NU0710", "NU0713", "NU0922", "NU0364", "NX0609", "NX0610"}
	);
	isThere = Function( {value},
		{},
		If( Contains( mylist, value ),
			1,
			0
		)
	);
	a = :JOBCODE Timepoint1;
	b = :JOBCODE Timepoint2;
	If(
		isThere( a ) & b == "", "Termination/Separation",
		a == "" & isThere( b ), "New Employee",
		isThere( a ) & isThere( b ), "No Change"
	);
);
Jim

View solution in original post

Highlighted
sagrim
Level II

Re: Create Categorical Variable Using Two Timepoints

I can't thank you enough for your help ! Not only does your code accomplish what I intended but your version is also much more efficient than mine. Nicely done sir !
Article Labels