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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.