Hi chuakp, Below is one style of coding.  Coding the various possible values of diagnosis codes saves the need for using the upper, lower or proper case functions. If you have to add codes or change the output locations of codes, it is easier to manipulate the proc format than the data step code. The notfound and error statements are redundant.  I added them just to show you what is possible.  If a new  'type' is ever added to SASHELP.CARS, the notfound dataset would contain any rows with the new type. proc format;   value $diagx      'SEDAN','Sedan','sedan'        = 1      'SUV','Suv','suv'                      = 2      'SPORTS','Sports','sports'    = 3      'WAGON','Wagon','wagon'    = 4      'TRUCK','Truck','truck'           = 5      'HYBRID','Hybrid','hybrid'      = 6       OTHER                                  = 7   run;; data want1 want2 want3 want4 want5 want6 notfound error;   set sashelp.cars; if put(type,$diagx.) = 1 then output want1; else if put(type,$diagx.) = 2 then output want2; else if put(type,$diagx.) = 3 then output want3; else if put(type,$diagx.) = 4 then output want4; else if put(type,$diagx.) = 5 then output want5; else if put(type,$diagx.) = 6 then output want6; else if put(type,$diagx.) = 7 then output notfound; else output error; run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 354:5 355:10 356:10 357:10 358:10 359:10 360:10 NOTE: There were 428 observations read from the data set SASHELP.CARS. NOTE: The data set WORK.WANT1 has 262 observations and 15 variables. NOTE: The data set WORK.WANT2 has 60 observations and 15 variables. NOTE: The data set WORK.WANT3 has 49 observations and 15 variables. NOTE: The data set WORK.WANT4 has 30 observations and 15 variables. NOTE: The data set WORK.WANT5 has 24 observations and 15 variables. NOTE: The data set WORK.WANT6 has 3 observations and 15 variables. NOTE: The data set WORK.NOTFOUND has 0 observations and 15 variables. NOTE: The data set WORK.ERROR has 0 observations and 15 variables. NOTE: DATA statement used (Total process time): real time 1.21 seconds cpu time 0.20 seconds ... View more

Hi, I solved the problem using a proc format style. I found it was easier to update and change the formats than the macros.  Also the format style provided me with the "OTHER" option. ... View more

Reeza, Your query does not retain the original order of the data in "WANT".  Please see my question at https://communities.sas.com/thread/57291. ... View more

Here's one way using a Proc Freq and a Proc SQL. proc freq data=have order=data noprint;         tables name / missing nocum nopercent out=havecount; run;       proc sql;   drop table work.haveboth;   CREATE TABLE work.haveboth as   SELECT a.name,          (select distinct count from work.havecount as b               where a.name = b.name) as count   FROM work.have as a    ; quit; ... View more

An order=data statement in the define statement also retains the original sort order. ... View more

Thank you all.  I have my solution.  When I applied Cynthia's solution, the report worked however the "dat." formatting was arranging the rows in the wrong order.  Using ideas I've found in Cynthia's papers, I applied the following code to achieve exactly what I need.  The first, non printed, group statement sorts the Year Month rows correctly. The second, printed, group statement displays the formatted Year Month correctly.    columns jj_year_month=test1 jj_year_month Plant_Tech,area,(count_nc count_nc_lots count_tested_plus_nc_lots); define test1                                      / group 'J&J Year/Month 1 ' noprint; define jj_year_month                        / group 'J&J Year/Month' format=dat.; define plant_tech                              / across 'Plant/Technology' width=25; define Area                                       / across 'Area' width=25; define count_nc                                / analysis 'No. NCs' format=6.0; define count_nc_lots                        / analysis 'No. NC Lots' format=6.0; define count_tested_plus_nc_lots  / analysis 'No. Lots Actioned' format=6.0; ... View more

BallardW, Thank you.  I never learned how to use the Proc Report Windowing environment. Cynthia, Thank you.  You provided what I needed.  I rely on many of the Report papers that you've written.  Please keep at it!  I was trying to follow Paper 388-2014 "Sailing Over the ACROSS Hurdle in PROC REPORT" using "Output 8" as my guide but I was failing. When I had the column statement correct, I was incorrectly coding the define statements.  When my define statements were correct, my column statement was wrong.  The statements below work perfectly.   columns jj_year_month Plant_Tech,area,(count_nc count_nc_lots count_tested_plus_nc_lots);         define jj_year_month                       / group    'J&J Year/Month'       format=dat. width=15;         define plant_tech                             / across   'Plant/Technology'    width=25;                                 define Area                                      / across   'Area'                          width=25;                                 define count_nc                               / analysis 'No. NCs'                   format=6.0;         define count_nc_lots                       / analysis 'No. NC Lots'            format=6.0;         define count_tested_plus_nc_lots / analysis 'No. Lots Actioned'  format=6.0; ... View more

What is your definition of top 500?.  You sorted the file.  Do you want the first 500 in each sort group?  Do you want the top 500 sort groups in terms of frequency or percentage of the total?      A proc freq can tell you which sorted values are more frequent than others. ... View more

This can be refined, but this works without using the Reverse function. data _null_; String= 'abcdefghijklmnopqrstuvwxyz'; stringlen = length(string); length reversestring $ 26;  /* ideally this would be set to be the same length of stringlen */ length onevalue $ 1;     do i=1 to stringlen;        if i=1 then do;            reversestring = substr(string,stringlen,1);        end;        else do;           if i ne stringlen then do;            position = stringlen - i;            onevalue = substr(string,position,1);            reversestring = cats(reversestring,onevalue);           end;        end;     end;   put reversestring=; run; reversestring=zxwvutsrqponmlkjihgfedcba ... View more

PG, Please forgive my ignorance.  I am trying to understand a neat concept.  Your note and what the SAS book says are confusing me.  I understand  that any posiitve number greater than zero is "true" and zero and negative numbers are "false".  I do not understand how "sum(dart_new=1)" is equivalent to "where dart_new=1".  If I understand the SQL code above dart_new is a variable containing a numeric value of 1 or zero and the sum(dart_new) statement in the unioned query will sum all the non-missing values of dart_new. Note: If you want to see the cases where dart_tot=0, you should use sum(dart_new=1) as dart_tot and remove the where clause. SUM Function (SAS 9.3 Functions and Call Routines Reference Volume 2. Page 877 "Returns the sum of the nonmissing arguments". ... View more