re: assign department codes

[twildone]

Hi...I am trying to create a new variable Department1 based on the variable Dpt and the Department codes in the list. For example, Department codes 'LM' and 'HRLD' would have Department1 = 'HRLD' which is grouped by StudentUID and Term. I can't seem to get it to populate the dataset for all records. Any suggestions?

data Have;
    length StudentUID 8 Term $ 5 Dpt 8 Department $ 4;
	infile datalines4 dlm=',' missover dsd;
    input StudentUID :best32. Term :$char5. Dpt :best32. Department :$char4.;
datalines4;
127389,19-20,16,HRLD
127389,19-20,22,IPG
127389,19-20, ,LM
127389,20-21,54,ES
127389,20-21,16,HRLD
127389,20-21, ,LM
127389,20-21,53,PT
;;;;

data Want;
    length Department1 $ 4 Dpt1 8;
    format Department1 $char4.;
	set Have;
	by StudentUID Term;
			if Dpt=16 and Department in ('LM','HRLD') then do;
					Department1 = 'HRLD';
					Dpt1=16;
			end;
			if Dpt=22 and Department in ('IPG') then do;
					Department1 = 'IPG';
					Dpt1=22;
			end;
			if Dpt=53 and Department in ('PT','PT2','PTD') then do;
					Department1 = 'PT';
					Dpt1=53;
			end;
run;

Want:
StudentUID	Term	DPT	Department	DPT1	Department1
127389	19-20	16	HRLD	16	HRLD
127389	19-20	22	IPG	22	IPG
127389	19-20		LM	16	HRLD
127389	20-21	54	ES	54	ES
127389	20-21	16	HRLD	16	HRLD
127389	20-21		LM	16	HRLD
127389	20-21	53	PT	53	PT

[jimbarbour]

You're getting missing values because

1.  You have missing values in a numeric field (Dpt)

and

2.  Because you have no logic for Department = ES 

You need to tell SAS what to do if there is a missing value in DPT, and you need to tell SAS how to handle Department=ES.

Jim

[jimbarbour · Accepted solution]

Here's a start, below.  This probably is NOT what you want, at least not completely.  I'm just trying to give you some ideas.

1.  I have joined the IF statements together with an ELSE so that they are mutually exclusive.

2.  I have a final ELSE so that if no condition is met, we'll see an "unkn" (unknown) in the results so at least we'll know what's going on.

3.  I coded an action for 'ES'.  This may be what you want, so correct as necessary.

4.  As an example, I broke apart the Department and the Dpt IF statements for LM/HRLD.  The way they were, if the Dpt was missing, you got nothing.  I set up a default in the case that the Dpt is not 16.  This may not be what you want; this is just an example.

In an IF statement, you need to handle every possible combination of conditions -- and handle what happens if none of the conditions are met.

The results are below the code.  Note that now all the rows have Department1 populated.

data Want;
    length Department1 $ 4 Dpt1 8;
    format Department1 $char4.;
	set Have;
	by StudentUID Term;
			if Department in ('LM','HRLD') then do;
				IF  Dpt	= 16  then
					do;
						Department1 = 'HRLD';
						Dpt1=16;		
					end;
				else
					do;
						Department1 = Department;
						Dpt1 = Dpt;
					end;
			end;
			ELSE
			if Department in ('IPG') then do;
					Department1 = 'IPG';
					Dpt1=22;
			end;
			ELSE
			if Department in ('PT','PT2','PTD') then do;
					Department1 = 'PT';
					Dpt1=53;
			end;
			ELSE
			IF	Department	=	'ES'	THEN
				DO;
					Department1 = 'ES';
					Dpt1 = 54;
				END;
			ELSE
				DO;
					Department1 = 'Unkn';
					Dpt1 = 99;
				END;
run;

Jim