This is the correct simulation (an error occur in the previous one) I have a table like this : obs position Level HC 1 1 1 1 2 2 2 1 3 3 3 2 4 4 4 1 5 5 2 1 6 6 3 1 7 7 4 1 8 8 4 2 I want to calculate by position the sum of head count under that position based on the hierarchy level seen in the column Level, like this : obs position Level HC HC for position under 1 1 1 1 9 2 2 2 1 3 (sum of headcount until reach the same level without crossing lower value of column Level) 3 3 3 2 1 4 4 4 1 0 (lowest level so 0 position under, but the lowest level could change (could become 5, 6 or more)) 5 5 2 1 4 6 6 3 1 3 7 7 4 1 0 8 8 4 2 0 I have tried multiple combination of looping and condition but I can't seem to wrap my head around the solution. Can someone help me on this? Thank you so much SAS community ... View more

you are right it should be 3 ... View more

Here's the log that I get from the error: 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH='R:\XXXX\REPORTS 5 ! COMPLETED\0000_ThursdayRecurring\ThursdayRecurring.egp'; 6 %LET _CLIENTPROJECTPATHHOST='XXXXX'; 7 %LET _CLIENTPROJECTNAME='ThursdayRecurring.egp'; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HtmlBlue 17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 25 GOPTIONS ACCESSIBLE; 26 PROC EXPORT DATA=WORK.FILTER_FOR_QUERY_FOR_SSC_EMPL_AN 27 FILE="R:\XXXX\REPORTS 27 ! COMPLETED\0000_ThursdayRecurring\Output Files\&Tracker_4147._SSC_4147_EMPL_AND_POS_SDMB_FINAL_&Date_Prod_Today." WARNING: Apparent symbolic reference TRACKER_4147 not resolved. WARNING: Apparent symbolic reference DATE_PROD_TODAY not resolved. 28 DBMS=EXCEL REPLACE; ERROR: DBMS type EXCEL not valid for export. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 29 RUN; 30 31 GOPTIONS NOACCESSIBLE; 32 %LET _CLIENTTASKLABEL=; 33 %LET _CLIENTPROCESSFLOWNAME=; 34 %LET _CLIENTPROJECTPATH=; 35 %LET _CLIENTPROJECTPATHHOST=; 36 %LET _CLIENTPROJECTNAME=; 37 %LET _SASPROGRAMFILE=; 38 %LET _SASPROGRAMFILEHOST=; 39 40 ;*';*";*/;quit;run; 41 ODS _ALL_ CLOSE; 42 43 44 QUIT; RUN; ... View more

Hello, Thank you for your solution. However I still get an error message. Here's my code: PROC EXPORT DATA=WORK.FILTER_FOR_QUERY_FOR_SSC_EMPL_AN FILE="filepath\&Tracker_4147._filename_&Date_Prod_Today." DBMS=EXCEL REPLACE; RUN; I get the following error : ERROR: Connect: Class not registered ERROR: Error in the LIBNAME statement. Do you know what cause this? Note that SAS/Access to PC files is licensed and I use SAS EG 7.1 ... View more