Hello, this is similar to another thread i posted before here: https://communities.sas.com/t5/SAS-Programming/How-to-check-for-an-array-of-columns-and-search-for-specific/m-p/887340#M350582 and i am sorry but i can't figure out how to flag the rows when a specific pattern occurs. Basically I want to flag any row that have a sequence of the following pattern: 0 --> D or 4 or 6 or 9 --> 0 ---> 2. Data structure is the same as in the pasted link above. There could be other values in between those sequence (which is okay) so it's not necessarily they are right next to each other.    Below is the code I've trying to make it work but it is not. Somehow it's not capturing the second time 0 appears in the rows. Any suggestions? Thanks!! DATA TEST3; SET TEST2; ARRAY COLNAME st1-st42; FLG = 0; FLG1 = 0; FLG2 = 0; i=whichc('0', of colname[*]); if i then do i = i + 1 to dim(colname) until (flg); flg = colname[i] IN ('D', '4', '6', '9'); end; if flg then do i = i + 1 to dim(colname) until (flg1); flg1 = colname[i] = '0'; end; if flg1 then do i = i + 1 to dim(colname) until (flg2); flg2 = colname[i] = '2'; end; if flg and flg1 and flg2; DROP FLG FLG1 FLG2 i; run; ... View more

Hello folks, I have a large volume of tables to process and basically I have the main body of the code ready, but I don't know how to set up a macro to let the whole part of code run by each center. Below is the beginning of my code:   *Macros; %LET Regctr = WS; %LET fy = 2022-2023; %LET RCFULL = Full name of the center; *Import excel file for data source; proc import out=&Regctr. datafile= "&inpath\&Regctr..xlsx" dbms=xlsx replace; sheet="_&Regctr"; getnames=yes; run; *SEPARATE CONTRACTS; DATA &Regctr.; SET &Regctr.; UCI_0=INPUT(UCI,12.); RUN; DATA &Regctr.; SET &Regctr.; WHERE UCI_0 NE . ; RUN; RUN; Main bulk of the code continue to the very end ... and then at the proc report step below: *---------------------------------SAS output, complete version, separate by tabs------------------------; ODS EXCEL FILE='Tables.xlsx' OPTIONS(SHEET_INTERVAL='none' SHEET_NAME='table1' EMBEDDED_TITLES='yes' TAB_COLOR='green' START_AT='2,4' FROZENHEADERS='yes'); OPTIONS MISSING='0'; ODS ESCAPECHAR='^'; title1 j=center bold font=calibri "my title"; title2 j=center bold font=calibri "some other title"; title3 j=right italic font=calibri "Fiscal Year &fy"; title4 j=right italic font=calibri "Page 1 of 1"; title5 j=left italic font=calibri "&RCFull"; title6 j=left bold font=calibri "All Ages"; PROC REPORT DATA=tbl1 NOWD HEADSKIP HEADLINE SPACING=1 STYLE(report)={width=100%} STYLE(column)=[background=white fontstyle=roman fontsize=3 fontweight=medium fontfamily='calibri'] STYLE(header)=[background=white fontstyle=roman fontsize=3 fontweight=bold fontfamily='calibri'] STYLE(lines)=[background=white fontstyle=roman fontweight=medium fontfamily='calibri' color=black] ; ... As you can see, I have a total of 21 centers that I need to run the same code over again, and  below is what i am trying to resolve: how could I set up the macro so that it grabs the two-letter abbreviations (in the above example, WS needs to be changed to another two letter abbreviation such as AB, CB, EB, etc when WS part has finished running) and then also change the full name accordingly? by the time the code runs towards the end, i also need the macro to save the tables using the two-letter abbreviation, for example, change the current part ODS EXCEL FILE='Tables.xlsx'   to something like  ODS EXCEL FILE='WS.xlsx' and go on...   I feel this might be something easy to accomplish but i can't figure out a proper way, any ideas? Thank you so much! ... View more

Hello folks, I need to mask values of three data columns using the following condition: if the value is less than 11 and not equal to 0, mask with one asterisk (primary suppression) if only one value was masked, mask the second smallest value (complementary suppression) the table (named: tbl25) i am trying to mask values looks like below, data points are made-up:   I figured out a very clumsy way of doing it and below is my code, as you can see there're three issues with it: i have to mask the three columns one by one: nopos, yespos, and totcnt i also noticed that my "eth" column was missing in the following code after i did the 3 columns separately, i had to merge them back again to come up with something like tbl25  Given this is just one of the many tables i need to mask values, can anyone provide a concise method to achieve the same goal? many many thanks!!! data want1; flg=0; array s(8); do i = 1 to 8; set tbl25; if nopos LE 11 AND NOPOS NE 0 then flg=1; else s(i) = NOPOS; /*<11 became missing*/ end; if nmiss(flg) ne 1 then do; second = min(of s(*)); end; do i = 1 to 8; if s(i) = second and second ne 0 then do; s(i)=-100; /*complementary became -100*/ end; end; do i = 1 to 8; nopos = s(i); output; end; keep nopos; run; **; data want2; array s(8); do i = 1 to 8; set tbl25; if yespos LE 11 AND yesPOS NE 0 then flg=1; else s(i) = yesPOS; /*<11 became missing*/ end; if nmiss(flg) ne 1 then do; second = min(of s(*)); end; do i = 1 to 8; if s(i) = second and second ne 0 then do; s(i)=-100; /*complementary became -100*/ end; end; do i = 1 to 8; yespos = s(i); output; end; keep yespos; run; **; data want3; array s(8); do i = 1 to 8; set tbl25; if totcnt LE 11 AND totcnt NE 0 then flg=1; else s(i) = totcnt; /*<11 became missing*/ end; if nmiss(flg) ne 1 then do; second = min(of s(*)); end; do i = 1 to 8; if s(i) = second and second ne 0 then do; s(i)=-100; /*complementary became -100*/ end; end; do i = 1 to 8; totcnt = s(i); output; end; keep totcnt; run; data want1; set want1; id=_N_; run; data want2; set want2; id=_N_; run; data want3; set want3; id=_N_; run; proc sort data=want1; by id; run; proc sort data=want2; by id; run; proc sort data=want3; by id; run; data all; merge want1(in=a) want2(in=b) want3(in=c); by id; if a or b or c; run; ... View more

Hello folks, I think I've figured partially out about this issue at hand and it should be straightforward. Basically, I want to search for values in an array of 21 columns and make sure I capture rows when event A occurs BEFORE event B. In my case, status 1 occurs before status 0, but not necessarily in adjacent columns. Below is what my data looks like: each row is a unique ID, and I have column names from st1 till st21: I want to identify/signal the rows when value 1 occurs BEFORE value 0 per row and there could be other values in between the two values I want, but as long as 1 is before 0, I want to flag the row. Below is my code: DATA TEST; SET KEEP4; ARRAY COLNAME{21} ST1-ST21; FLG=0; DO i=1 TO DIM(COLNAME); IF COLNAME{i} ='1' THEN DO; IF COLNAME{i+1}='0' THEN DO; FLG=1; END; END; END; DROP i; RUN; DATA TEST1; SET TEST; WHERE FLG=1; RUN; But the code above only selects the rows when 1 occurs RIGHT before 0 like below:  There will be situations when 1 occurs, followed by a bunch of other values before 0 appears, how can I capture those?    Many thanks! ... View more