In SAS 9.3 I'm creating a table in Proc Report and would like to display the values for a variable named "country" with all values (country names and their corresponding counts) displayed unless the country represents <20% of the total count. My dataset is huge, so I've created a sample of the data for illustration. SAMPLE DATA PersonID country count 1 Australia 1 2 USA 1 3 Australia 1 4 Canada 1 5 Canada 1 6 Canada 1 5 Germany 1 I created new variable "countrycount" to sum totals for each country. proc sql /*noprint */; create table finalsql as select * , sum(count) as countrycount label='Country Count' from final group by country quit; to give me output: PersonID country count countrycount 1 Australia 1 2 2 USA 1 1 3 Australia 1 2 4 Canada 1 3 5 Canada 1 3 6 Canada 1 3 5 Germany 1 1 I would like to show a table where Canada and Australia are displayed, and USA and Germany are collapsed into the 'other' category. I'd like to display the total counts by country, like this: TABLE Country Total Canada 3 Australia 2 Other 2 I am trying to automate report generation, and the arrival data will be changing monthly, so I need a dynamic process to collapse countries into the "other" category. Is there a way to create a format that would essentially work like this? My challenge is to create a format based on the values from two variables, one character and one numeric. OR is there another way to do this? /*I realize this wouldn't actually work in proc format - hope it helps explains my question*/ proc format value $country_collapsed country = country /* do this for all values where countrycount >=20% of total */ country totals <20% = 'other'; /* collapse all countries into 'other' category if countrycount <20% total */ run;
... View more
