About Sangita1

Sangita1 · ‎12-05-2020

Hi, Thank you so much. This is working great. Appreciate you sharing knowledge and spending your precious time in helping me. I have a question - for my insert the missing terms in between the given terms for any student uid, I went around the dirty way - meaning I created a time series and a counter. I did a cross join and merge to insert all the rows for all the time periods and then I deleted the rows which were above and beyond the first and last row (with the data). It took 4 to 5 data steps but it works fine. I am sure there is a better way, but I don't have a clue as to what function/procedure call might be useful. Again, thanks for your help.

Sangita1 · ‎12-04-2020

Hi All, I was able to create the table (not the most elegant way) but somehow, where now I have all the "wanted" rows for each uid. I can also identify which rows are original and which ones are created by code (orig_row column value). My dataset is large and have more than 60 columns. When I insert a new row for the missing timeperiod, I am only populating 5 columns, so rest of the 55 columns are blank and I have to copy the value from the previous row. I read about copying multiple values when the column names are written as arrays but my column names are all different (Race, Ethnicity, Gender etc..). I have to copy , say 30 columns from the row above. I can arrange the columns so all the columns that need the value are at the end of the table. Is there a way to do it or do I have to do Retain statement for each variable/column. Thanks

Sangita1 · ‎12-04-2020

Sorry, for the third row for uid U1, timeid is 5 and not 4. and timeid 5 corresponds to 15-SEP-11.

Sangita1 · ‎12-04-2020

Hi, Thanks for your help. I was trying something also, that if I have an ID associated with the time period series then may be there is a "Do Missing loop" that I can use to fill in the missing time period. So, yes, here is the sample data which creates the two tables and then join the table : Please let me know if it makes sense. Thank you. data TestData; infile datalines truncover; input uID:$8. Semester:ddmmyy10. Code:$32. residence:32. OrigRow:32.; format Semester date9.; datalines; u1 150111 G 12 1 u1 150112 G 12 1 u1 150913 G 12 1 u2 150111 B 23 1 u3 150912 B 31 1 u3 150514 G 31 1 u4 150511 G 14 1 u4 150112 G 14 1 u4 150113 G 14 1 u4 150913 G 14 1 ;;;;;;;;;; Data Time_Period_series; %let start_date=15May2010; %let end_date=15Sep2020; Time_Period="&start_date"d; Timeid = 1; do while (Time_Period<="&end_date"d); output; Time_Period=intnx('month', Time_Period,4 , 's'); Timeid = Timeid +1; end; format Time_Period date9.; run; proc sql; create table append1 as select d.uID, t.Timeid, d.Semester, d.code, d.residence, d.OrigRow from TestData d, Time_Period_series t where d.Semester = t.Time_Period; quit; This gives me a table like uID Timeid Semester Code residence OrigRow u1 3 15-Jan-11 G 12 1 u1 6 15-Jan-12 G 12 1 u1 11 15-Sep-13 G 12 1 u2 3 15-Jan-11 B 23 1 u3 8 15-Sep-12 B 31 1 u3 13 15-May-14 G 31 1 u4 4 15-May-11 G 14 1 u4 6 15-Jan-12 G 14 1 u4 9 15-Jan-13 G 14 1 u4 11 15-Sep-13 G 14 1 And I will like a table as: uID Timeid Semester Code residence OrigRow u1 3 15-Jan-11 G 12 1 u1 4 15-May-11 G 12 0 New Row created u1 4 15-Sep-11 G 12 0 New Row created u1 6 15-Jan-12 G 12 1 u1 7 15-May-12 G 12 0 New Row created u1 8 15-Sep-12 G 12 0 New Row created u1 9 15-Jan-13 G 12 0 New Row created u1 10 15-May-13 G 12 0 New Row created u1 11 15-Sep-13 G 12 1 u2 3 15-Jan-11 B 23 1 u3 8 15-Sep-12 B 31 1 u3 9 15-Jan-13 B 31 0 New Row created u3 10 15-May-13 B 31 0 New Row created u3 11 15-Sep-13 B 31 0 New Row created u3 12 15-Jan-14 B 31 0 New Row created u3 13 15-May-14 B 31 1 u4 4 15-May-11 G 14 1 u4 5 15-Sep-11 G 14 0 New Row created u4 6 15-Jan-12 G 14 1 u4 7 15-May-12 G 14 0 New Row created u4 8 15-Sep-12 G 14 0 New Row created u4 9 15-Jan-13 G 14 1 u4 10 15-May-13 G 14 0 New Row created u4 11 15-Sep-13 G 14 1

Sangita1 · ‎12-03-2020

Hi All, I have a table with student enrollment data. So in my table, each student id can have multiple rows, one row for each semester (we have three semesters per academic year). So a student can enroll in Fall 2015, and his enrollment ends in Summer 2019, so there will be 12 terms and potentially 12 rows in the table. However, if the student does not enroll in 5 terms in between, the table will have only 7 rows. I need to create the 5 missing rows based on the data from the row above for that student id (UID), so I have all 12 rows for that student. I have a column orig_row in the table which has all values as 1. When I copy the row, I need to mark this particular column value as 0 so I know that this row was copied from the previous row.. I have a second table with Time_periods that I can use as a lookup for missing semesters. So my data table looks like following: If a student enrolled only for one semester as uid2, then it will have only one row. Dept Residency_Type UID NewUId EndUId Uidchange Time_Period OrigRow Health 2-Out-of-State U1 1 0 New Uid 15-Sep-15 1 Health 2-Out-of-State U1 0 0 Enrolled 15-Jan-16 1 Health 2-Out-of-State U1 0 0 Enrolled 15-Jan-17 1 Health 2-Out-of-State U1 0 1 End Uid 15-Sep-17 1 Science 1-In-State U2 1 1 End Uid 15-Jan-13 1 A&L 1-In-State U3 1 0 New Uid 15-May-10 1 A&L 1-In-State U3 0 0 Enrolled 15-Sep-10 1 A&L 1-In-State U3 0 0 Enrolled 15-May-11 1 A&L 1-In-State U3 0 0 Enrolled 15-Sep-12 1 A&L 1-In-State U3 0 0 Enrolled 15-Jan-13 1 A&L 1-In-State U3 0 1 End Uid 15-May-14 1 And I want it to look like below Dept Residency_Type UID NewUId EndUId Uidchange Time_Period OrigRow Health 2-Out-of-State U1 1 0 New Uid 15-Sep-15 1 Health 2-Out-of-State U1 0 0 Enrolled 15-Jan-16 1 Health 2-Out-of-State U1 0 0 Enrolled 15-May-16 0 Health 2-Out-of-State U1 0 0 Enrolled 15-Sep-16 0 Health 2-Out-of-State U1 0 0 Enrolled 15-Jan-17 1 Health 2-Out-of-State U1 0 0 Enrolled 15-May-17 0 Health 2-Out-of-State U1 0 1 End Uid 15-Sep-17 1 Science 1-In-State U2 1 1 End Uid 15-Jan-13 1 A&L 1-In-State U3 1 0 New Uid 15-May-10 1 A&L 1-In-State U3 0 0 Enrolled 15-Sep-10 1 A&L 1-In-State U3 0 0 Enrolled 15-Jan-11 0 A&L 1-In-State U3 0 0 Enrolled 15-May-11 1 A&L 1-In-State U3 0 0 Enrolled 15-Sep-11 0 A&L 1-In-State U3 0 0 Enrolled 15-Jan-12 0 A&L 1-In-State U3 0 0 Enrolled 15-May-12 0 A&L 1-In-State U3 0 0 Enrolled 15-Sep-12 1 A&L 1-In-State U3 0 0 Enrolled 15-Jan-13 1 A&L 1-In-State U3 0 0 Enrolled 15-May-13 0 A&L 1-In-State U3 0 0 Enrolled 15-Sep-13 0 A&L 1-In-State U3 0 0 Enrolled 15-Jan-14 0 A&L 1-In-State U3 0 1 End Uid 15-May-14 1 I am new to programming and I tried to use set, merge, crossjoin, outer join etc but not able to get this result. Thank you.

Sangita1 · ‎12-02-2020

Actually, I was able to fix that error. Thanks

Sangita1 · ‎12-02-2020

Hi, I have one more question please. When I run the code on my big dataset, I am getting the type mismatch error ERROR: Type mismatch for key variable TERMID at line 68 column 6. I think it is because TERMID is a char. I am not sure if I should do a length statement or convert it (as some posts suggested). What might be the best way to resolve this? Thank you.

Sangita1 · ‎12-02-2020

Yes, I did. Both solutions are great. Now I have the code, and I am going to read the function calls so I know how/where to use them in future. Thankful for the help, as I was struggling for couple hours and reading how to use retain/lag. You made my day (or rather morning as it is very early morning for me).

Sangita1 · ‎12-02-2020

Thank you. Your code is working great also, but I think I can only accept one example as solution. I will spend time learning definekey, definedone and how elegantly you have written the solution. I really appreciate your help. Thank you very much for sharing your knowledge with the newbees like me. Regards.

Sangita1 · ‎12-02-2020

yes, I am new to programming, so I created the prevtermid column. Then I somehow was able to work it out by DATA work.identified; set work.sample; by ID; retain lagtermid; if first.id then new_student= 'New'; y = lagtermid; if prevtermid = lagtermid then retain_Student = 'Yes'; else retain_Student = 'No'; lagtermid = termid; If Substr (termid, 5, 2) = '05' then lagtermid = prevtermid; RUN; I was going to drop all the extra columns - this is call "running around the block to walk one step". Thank you so much for your elegant code. I am also going to read up the functions that you have used. Appreciate the super fast response.

Sangita1 · ‎12-02-2020

When I write the following code: It does not work properly because for the Fall term, I have the previous termid as the Spring term (which can be two rows above as some students take summer term and some do not take summer term). So I have to do something when it is summer term, as that can set the lag variable incorrectly. DATA work.identified; set work.sample; by ID; retain lagtermid; if first.id then X= 'New'; if prevtermid = lagtermid then retain_Student = 'Yes'; else retain_Student = 'No'; lagtermid = termid; RUN;

Sangita1 · ‎12-02-2020

Hi, I have following dataset (contains three different Ids) for students. I want to find out if they are retained or not based on if their record exists in the previous term. DATA work.sample; format id termid prevtermid; INPUT termid prevtermid id $; DATALINES; 201508 201501 U100 201601 201508 U100 201608 201601 U100 201701 201608 U100 201705 201701 U100 201708 201701 U100 201508 201501 U200 201608 201601 U200 201701 201608 U200 201705 201701 U200 201708 201701 U200 201508 201501 U300 201601 201508 U300 201701 201608 U300 201705 201701 U300 201708 201701 U300 ; RUN; proc sort data=work.sample; by id termid ; quit; So the sorted dataset will look like below: ID Termid prevtermid U100 201508 201501 U100 201601 201508 U100 201608 201601 U100 201701 201608 U100 201705 201701 U100 201708 201701 U200 201508 201501 U200 201608 201601 U200 201701 201608 U200 201705 201701 U200 201708 201701 U300 201508 201501 U300 201601 201508 U300 201701 201608 U300 201705 201701 U300 201708 201701 If for the same ID, prevTermID exists as a Termid in the previous rows, then I want to create a RetainFlag as Yes otherwise No. In the second iteration, if it is beginning of new Id (which will always have the "retainFlag = "no"), I want to mark this row as "New" to identify it as new student record. So the output should look like below: UID Termid prevtermid RetainFlag U100 201508 201501 New U100 201601 201508 Yes U100 201608 201601 Yes U100 201701 201608 Yes U100 201705 201701 Yes U100 201708 201701 Yes U200 201508 201501 New U200 201608 201601 No U200 201701 201608 Yes U200 201705 201701 Yes U200 201708 201701 Yes U300 201508 201501 New U300 201601 201508 Yes U300 201701 201608 No U300 201705 201701 Yes U300 201708 201701 Yes I am trying to use Lag and retain but am not being successful how to accomplish it. I will really appreciate your help. Thank you so much for your time and willingness to share your expertise.

Sangita1 · ‎11-05-2020

Hi Cynthia_sas, Proc tabulate did the magic and I was able to move forward. For some reason, Proc Report kept on erroring and I was not able to get that to work. I think Proc report gives more control for subtotals etc. which Proc Tabulate might not do. But for now, I can move forward. I really appreciate your help. Learning slowly as I go. Thank you.

Sangita1 · ‎11-01-2020

Hi, I have a dataset as below: Sample: For 3 Academic years, across 3 campuses I have Students which can fall into 4 different Status. I need to print a report. Currently I export the data in Excel and then create a pivot on the data where I have Rows as AY Year and Campus and Column as Retain Status and Values as Count of (term1_student). I know in SAS also we can create pivots and tables but I am not able to create the table. I am relatively new to SAS and have tried Proc Freq, Proc tabulate, proc report, tableeditor files but I keep on getting into some or other errors. Your help is greatly appreciated. AY Year Campus Retain_Status Term1_Student 2015-2016 Campus1 Retained uid1 2015-2016 Campus2 Retained uid2 2015-2016 Campus3 Retained uid3 2015-2016 Campus1 Withdrawn uid4 2015-2016 Campus1 Graduated uid5 2015-2016 Campus1 NOT Retained uid6 2015-2016 Campus2 Retained uid7 2015-2016 Campus3 Retained uid8 2015-2016 Campus2 Withdrawn uid9 2015-2016 Campus1 Graduated uid10 2016-2017 Campus1 Retained uid11 2016-2017 Campus2 Retained uid12 2016-2017 Campus3 Retained uid13 2016-2017 Campus1 Withdrawn uid14 2016-2017 Campus1 Graduated uid15 2016-2017 Campus1 NOT Retained uid16 2016-2017 Campus2 Retained uid17 2016-2017 Campus3 Retained uid18 2016-2017 Campus2 Withdrawn uid19 2016-2017 Campus1 Graduated uid20 2015-2016 Campus1 Retained uid21 2017-2018 Campus2 Retained uid22 2017-2018 Campus3 Retained uid23 2017-2018 Campus1 Withdrawn uid24 2017-2018 Campus1 Graduated uid25 2017-2018 Campus1 NOT Retained uid26 2017-2018 Campus2 Retained uid27 2017-2018 Campus3 Retained uid28 2017-2018 Campus2 Withdrawn uid29 2017-2018 Campus1 Graduated uid30 I want the report to look like below (The data values below are just made up in the below example but they are the number of students that fall into that status for the campus within Academic Year): Graduated Retained Withdrawn Not Retained 2015-2016 Campus1 2 1 3 2 Campus2 1 4 1 Campus3 2 1 1 2016-2017 Campus1 2 1 3 2 Campus2 1 4 1 Campus3 2 1 1 2017-2018 Campus1 2 1 3 2 Campus2 1 4 1 Campus3 2 1 1 Thank you.

Online Status	Offline
Date Last Visited	‎02-27-2021 05:26 AM

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Inserting rows in a table based on missing column values.

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Colorized Text in the EG Log?

Re: Inserting rows in a table based on missing column values.

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Re: Inserting rows in a table based on missing column values.

Inserting rows in a table based on missing column values.

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Re: Comparing rows within a table based on ID and create a flag

Comparing rows within a table based on ID and create a flag

Re: Presenting data in a tabular format using SAS EG (Proc Freq, Proc ...

Presenting data in a tabular format using SAS EG (Proc Freq, Proc repo...