re: create new variables

[twildone]

Hi...I am trying to create two new variables (Major1 & Major2) from the variable Program. What I have is that some students finish one program and start a new program in the same AcademicYear and I want to separate the program into Major1 and Major2. If the student starts a second program in the same AcademicYear, then I want that program to be entered as Major2 otherwise the program is entered as Major1. Any suggestions....Thanks

data Have;
length AcademicYear $ 4 ID $ 4 Program $ 20 TermStartDate $ 8;
input AcademicYear $ ID $ Program $ TermStartDate $;
cards;

2019 0010 Hairstyling 20190901
2019 0010 Hairstyling 20200101
2019 0010 CulinaryArt 20200401
2020 0010 CulinaryArt 20200901
2020 0010 CulinaryArt 20210101
2019 0015 AutoMechanics 20190901
2019 0015 AutoMechanics 20200101
;
run;

Want:

AcademicYear	ID	Program	TermStartDate	Major1 	Major2
2019	0010	Hairstyling	20190901	Hairstyling	
2019	0010	Hairstyling	20200101	Hairstyling	
2019	0010	CulinaryArt	20200401		CulinaryArt
2020	0010	CulinaryArt	20200901	CulinaryArt	
2020	0010	CulinaryArt	20210101	CulinaryArt	
2019	0015	AutoMechanics	20190901	AutoMechanics	
2019	0015	AutoMechanics	20200101	AutoMechanics	

[PaigeMiller]

Often, this type of re-arranging the data is not necessary, and makes the next steps more difficult. Leaving the data as is usually makes the data easier to use in the next step.

What analysis or report are you going to be creating?

[twildone]

Hi Paige....I have to create a report and one of the requirements is to identify the programs that a student would have been enrolled in during each academic year as Major1 and Major2 if they have enrolled in a second program in the same academic year....hope that helps....thanks

[PaigeMiller]

No re-arranging is needed. What should the report look like?

[twildone]

The report has to contain the Academic Year, ID, LastName, FirstName, MiddleName, Major1, Major2, StartDate, CompletionDateand Degree.

[Reeza]

With separate lines for each Major as in your example? That doesn't seem to make a ton of sense for future usage as shown to be honest.
2019 0010 Hairstyling 20200101 Hairstyling
2019 0010 CulinaryArt 20200401 CulinaryArt

[twildone]

Hi Reeza….for the example that you have identified, ID=0010 was enrolled in the 2019/20 academic year and on January 1, 2020, this student was enrolled in the Hairstyling program. However, on April 1, 2020, the student was enrolled in the CulinaryArt program which would mean that the student had finished the Hairstyling program before April 1, 2020 and started the new program of Culinary Art program on or after April 1, 2020. The academic year of 2019 starts on July 1, 2019 and ends June 30, 2020. Hope that explains this example. Thanks.

[ballardw · Accepted solution]

---

@twildone wrote:

The report has to contain the Academic Year, ID, LastName, FirstName, MiddleName, Major1, Major2, StartDate, CompletionDateand Degree.

---

Some of this probably becomes a lot easier if your Dates are actually date values.You have multiple "termstartdates". Which would be the actual Startdate for the report? And what would be the rule for Completion date? Your "dates" are all "start". So no term end date. Where is the completion date to come from?

Are you sure that none of these records have 3 or more programs in an academic year?

This code, for your very limited example, adds a MajorNumber value PER ACADEMIC YEAR to count them.

proc sort data=have;
   by id AcademicYear TermStartDate  ;
run;
data use;
   set have;
   by  id AcademicYear TermStartDate ;
   lp=lag(program);
   retain MajorNumber;
   if first.academicyear then MajorNumber=1;
   else if program ne lp then MajorNumber+1;
   drop lp;
run;

But without seeing a concrete example of what the actual report is supposed to contain based on the example date I am not sure of the best way to use this.

Where would we get "degree"? Don't see anything in your example that would indicate when a "degree" would be completed.

[twildone]

Hi BallardW….I am quite sure that no student would have enrolled in more than 2 programs in the same academic year because the duration of programs are between 6 and 10 months. I did edit the data to include another program for ID=0010 in AcademicYear=2019 so that there were 3 programs and ran your code and it still worked. Thank you so much.

data use;
length Major1 $ 20 Major2 $ 20;
   set have;
   by  id AcademicYear TermStartDate ;
   lp=lag(program);
   retain MajorNumber;
   if first.academicyear then MajorNumber=1;
   else if program ne lp then MajorNumber+1;
   if MajorNumber = 1 then 
   Major1 = Program; 
   else
Major2 = Program; 
   drop lp;
run;