I try to create two macro variables to use throughout my programs. So the first gave me a value of 201505, and the second one gave me a list 15 terms up until 20201. These are what I want. However, when I execute the macro variables in later program. It only gave me one term information, which is the 201505 information. It might be because the TERMID is used twice in the macros and sas only recognizes the one used in first marco. What I really want to include in later program is the list of 15 terms generated by the second macro. Any ideas on how to modify the macros? Thanks!   /*The first one calculates the term which is 5 years before*/ %GLOBAL Term_From ; PROC SQL; SELECT (input(termid, best6.) -503) INTO :Term_From FROM SID.SID_Termid where Flagcurrentterm='Y'; quit; /*  The second one selects a list of 15  terms that could be included in later programs*/ %GLOBAL TERMCODE; PROC SQL; SELECT TERMID INTO :TERMCODE FROM SID.SID_Termid WHERE TERMID IN (select a.termid from SID.SID_Termid a where a.latestbenchmark = 'E' AND a.termid ge Put((&Term_From), 6.)); QUIT; /* the following programs will execute macro variables*/ Proc sql; Create Table Crs_records AS SELECT count(*) AS Num_rec FROM SID.SID_COURSES_TAKEN CRS WHERE fee_class_kind ^= 'P' AND termid in ("&TERMCODE"); Create table crs_rec_by_terms_bench AS SELECT termid, bench, count(distinct UID) AS Num_rec FROM SID.SID_COURSES_TAKEN CRS WHERE fee_class_kind ^= 'P' AND termid in ("&TERMCODE") GROUP BY termid, bench ORDER BY termid Desc, bench; quit;   ... View more

proc sql; create table JDC_Graduation as select T2.MAJOR_CODE, count(distinct T1.UID) as InitialCohort, count(distinct T2.BA_GRAD_4_YEAR) as BA_4_YEAR, (calculated BA_4_YEAR/calculated InitialCohort)*100 as JDC_GRAD_Rate_4YR, count(distinct T2.BA_GRAD_5_YEAR) as BA_5_YEAR, (calculated BA_5_YEAR/calculated InitialCohort)*100 as JDC_GRAD_Rate_5YR, count(distinct T2.BA_GRAD_6_YEAR) as BA_6_YEAR, (calculated BA_6_YEAR/calculated InitialCohort)*100 as JDC_GRAD_Rate_6YR from Work.Journalism_Graduation (where=(STU_RECENT_ADM_TYP ='B' AND MAJOR IN ('MJM', 'MDJ', 'COM','MAS','JMS'))) T1 LEFT JOIN Work.Journalism_Graduation (where=(MAJOR_CODE IN ('MJM', 'MDJ', 'COM','MAS','JMS'))) T2 ON TI.UID = T2.UID GROUP BY T2.MAJOR_CODE; quit;   I need help with this error message in sas log. How should I fix the syntax? Thanks. ... View more

I try to assign a list values to a macro variable, but somehow sas does not pick up all the values in that variable. How should I modify the code for %let statement? Thanks. %let admdate (LIST= 201505 201508 201601) proc sql; create table T1 as select id, major, college from Enrollment where term ='&admdate'; quit; ... View more

I need to do a calculation for each level of detail in proc sql: count number of courses for each ID, and sum number of Failed for each ID. Here is a sample of data. Notice that COURSE is not a numeric variable, so I cannot use proc summary or proc means. Does anyone have a better idea? Thanks!   ID COURSE Failed 1 NGR-7892 1 1 NGR-7945 0 1 NGR-7974 0 2 LIS-5973 0 2 LIS-5942 1 2 NGR-6207 0 2 TTE-6655 1 ... View more

I need to add a filter using a date variable which is in character format (e.g., 2017-07-01 00:00:00). Here is my sas code. I keep getting error message in sas log: ERROR: CLI cursor fetch error: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string. I know there is something wrong with the date format in where clause but not sure how to modify it. Anyone can help me with this? Thanks. proc sql; create table EMPLOYEE as select A.EMPLID, A.USF_ID, A.LAST_NAME, A.FIRST_NAME, A.SEX_DESC, A.ETHNIC_GROUP_DESC, A.JOB_EFFDT, A.HIRE_DATE, A.REHIRE_DATE, A.SERVICE_DATE, A.TERMINATION_DATE, A.POSITION_NBR, A.POSITION_EFFDT, A.POSITION_DESC, A.POSITION_ACTION_DESC, A.POSITION_ACTION_DATE, A.DEPTID, A.DEPTID_DESC, A.VP_AREA_DESC, P.PAY_END_DT as PayPeriodEndDate FROM Dwhouse.Gems_ps_pay_check_d3 P LEFT JOIN Dwhouse.Gems_appointment_d3 A on P.EMPLID = A.EMPLID where A.LOCATION_CODE = '04' and P.PAY_END_DT >= '2019-07-01' order by EMPLID, SERVICE_DATE; quit; ... View more