BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

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;

jimbarbour_0-1623722365729.png

Jim

View solution in original post

2 REPLIES 2
jimbarbour
Meteorite | Level 14

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
Meteorite | Level 14

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;

jimbarbour_0-1623722365729.png

Jim

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 398 views
  • 0 likes
  • 2 in conversation