I’m running into a puzzling group by query. When I run the query with the columns not commented out I receive done number for the sum of ret_1 when I run the same query with the additional columns I receive a bigger number. Thoughts on why?
proc sql;
create table ret_deg4test2 as
select a.cohort, a.person_uid, a.gender, a.gender_desc, a.residency, a.RESIDENCY_DESC, a.SCHEV_ETHNICITY_DESC,
b.college, b.COLLEGE_DESC_DISPLAY as COLLEGE_DESC,
a.pell_eligible, a.pell_recipient,
/*a.schev_student_status,
a.FIRST_GENERATION, a.HS_GPA, a.HS_GPA_RANGE, a.HS_GPA_RANGE_ID, a.POST_SEC_SCHOOL_GPA, a.SAT_MATH, a.SAT_RANGE, a.SAT_TOTAL,
a.SAT_TOTAL_RANGE_ID, a.SAT_VERBAL, a.SECONDARY_SCHOOL_PERCENT_RANGE, a.SECONDARY_SCHOOL_PERCENTILE,
a.academic_origin, a.academic_origin_id,*/
/*Annual fall to fall measures*/
sum(a.ret_1) as ret_1
from ret_deg3 a
left join ODSSTU.DAR_CAMPUS b on a.first_college=b.COLLEGE
group by a.cohort, a.person_uid, a.gender, a.gender_desc, a.residency, a.RESIDENCY_DESC, a.SCHEV_ETHNICITY_DESC,
b.college, b.COLLEGE_DESC_DISPLAY, a.pell_eligible, a.pell_recipient/*,
a.schev_student_status,
a.FIRST_GENERATION, a.HS_GPA, a.HS_GPA_RANGE, a.HS_GPA_RANGE_ID, a.POST_SEC_SCHOOL_GPA, a.SAT_MATH, a.SAT_RANGE, a.SAT_TOTAL,
a.SAT_TOTAL_RANGE_ID, a.SAT_VERBAL, a.SECONDARY_SCHOOL_PERCENT_RANGE, a.SECONDARY_SCHOOL_PERCENTILE*/;
quit;
The difference is because a.academic_origin, a.academic_origin_id was missing from the group by. There is a bug where the whole sql statment script should have errored out please fix in furture versions.
I noticed that when I take the join out that the number is correct not sure why the number of variables is causing this though.
The difference is because a.academic_origin, a.academic_origin_id was missing from the group by. There is a bug where the whole sql statment script should have errored out please fix in furture versions.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.