hi ... as Art said "nice"  ... it reminded me of another one of your ideas, the self-interleave (once GROUPNUM is added to the data set) ... data want (drop=groupnum); do _n_ = 0 by 0 until (last.groupnum) ;   set grouped (in=one) grouped;   by groupnum;   _n_ + one;   if ^one and _n_ ge 3 then output; end; run; ... View more

hi ... if you do not have a record number on each observation and you'd rather not use unsupported monotonic(), how about ... proc sql; select count(*) into :lastobs from have; create table want as select * from have (obs=1) union select * from have (firstobs=&lastobs); quit; ... View more

hi ... could try unsupported monotonic() data x; input id dol @@; datalines; 0 9 1 5 1 8 1 10 1 3 2 10 3 4 ; proc sql; select * from x where id eq 1 and monotonic() gt 1; quit; id       dol 1         8 1        10 1         3 ... View more

hi ... when a missing value is grouped with non-mssing values via a format, they all end up as missing in PROC FREQ proc format; value x 1-3 = '<4' other = '4+' ; run; data x; input x @@; datalines; 1 2 3 . 5 6 7 8 ; run; proc freq data=x; table x; format x x.; run;                                Cumulative    Cumulative x    Frequency     Percent     Frequency      Percent <4           3      100.00             3       100.00 Frequency Missing = 5 the notes I use for my intro SAS class ... http://www.albany.edu/~msz03/epi514/notes/fp125_140.pdf ... discuss this on pages 138 and 139 I've always called it the "bad apple rule" ... one missing value spoils all the other non-missing in the group ps  Art Carpenter has a nice write up on this ...  http://www.sascommunity.org/wiki/Missing_Values_in_Classification_Variables with a link ... http://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_freq_sect015.htm that states "When you use PROC FORMAT to create a user-written format that combines missing and nonmissing values into one category, PROC FREQ treats the entire category of formatted values as missing." ... View more

Hi ... thanks, that explains the OP table ... must not have any real missing values. As for your "not sure why" ... it doesn't get rid of missing since there are no missing values.  The table is based on the original values of the variables, not formatted values.  The OP must not have had any real missing values ... only the 77 and 99 that had a blank format label.  Since 77 and 99 are greater than 1,2,3,4 ... they ended up last. However, if there are no actual missing values, you cannot create missing data with a format and all observations end up in the table.  That BLANK value in the table using SASHELP.CLASS is just a mask for 16. If there actually were missing values in the OP data, they'd end up grouped with 77 and 99 and they'd all be considered as MISSING and would not be in the table ... View more

hi ... yes with SQL, but you still have to read/rewrite the data set anything that modifies the data set content rather than just the header (attributes such as variable names, formats, labels) requires a rewrite data x; retain name "mike"; run; title 'ORIGINAL'; proc contents data=x; ods select variables; run; proc sql; create table x as select name length=10 from x; quit; title 'MODIFIED'; proc contents data=x; ods select variables; run; ORIGINAL Alphabetic List of Variables and Attributes #    Variable    Type    Len 1    name        Char      4 MODIFIED Alphabetic List of Variables and Attributes #    Variable    Type    Len 1    name        Char     10 ... View more

hi ... one other idea if you know that the only variables that start with the string SCORE are in the list SCORE1, SCORE2, etc.,  you can also use ... total = sum (of score:); ... View more

hi ...(hey, I'm not Art) the ?? suppresses error messages in case the character string cannot be converted to a numeric value also ... #1 not sure why the NOT MISSING is needed (same for BEST ... I think that's been discussed here via a postingt by Howard Schreier) since that asks a question each time the statement is used versus just converting a missing character to a numeric when encountered #2 depending on the content of the character variable, you might also consisder using a COMMA informat try this and look at the LOG ... data x; input x : $5. @@; datalines; . 1000 999 200 abc 1e6 (200) 1,234 $88 1-2-3 ; run; data x; set x; x1 = input(x,32.); x2 = input(x, ?? 32.); x3 = input(x, ?? comma.); run;   x           x1         x2          x3                .          .           . 1000        1000       1000        1000 999          999        999         999 200          200        200         200 abc            .          .           . 1e6      1000000    1000000     1000000 (200)          .          .        -200 1,234          .          .        1234 $88            .          .          88 1-2-3          .          .         123 ... View more

hi ... the only way you get those missing values in your table is by adding the MISSING option in PROC FREQ (no matter where you use the FORMAT statement ... data step or PROC) ... using Linlin's data ... proc freq data=have; table v / missing; format v c5r.; run; did you use the MISSING option .. .if so, that's how the missing values end up in you table if you did, what I don't understand is how the missing values ended up listed LAST in your table ... if MISSING data are present in a table in PROC FREQ they are always listed first (for either numeric or character data) no matter what you do (use a format, use an ORDER= option, use a WEIGHT option which you must have to get the non-integer frequencies in your table, whatever) so, the table in your posting is a "mystery" also, a suggestion ... you can group the values that are assigned to missing with the format ... proc format; value C5R . , 77, 99  = " " 1 = "MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN" 2 = "FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN" 3 = "GRANDPARENT" 4 = "OTHER FAMILY MEMBER/FRIEND" ; run; ... View more