topic Re: top 10 frequency of one variable and "other" the rest in SAS Programming

top 10 frequency of one variable and "other" the rest

Cooksam13 — Fri, 29 Apr 2022 23:30:41 GMT

Hello,

i have done this to find the top ten countries with the highest frequency

%let TopN = 10;
proc freq data=new.resultinterp ORDER=FREQ;
tables A_BirthCountry / maxlevels=&TopN Plots=FreqPlot;
run;

and then I want to put the rest in an "other" category so i started with this data step

data country;
set new.resultinterp;
If A_BirthCountry = not("MYANMAR (BURMA)" or "IRAQ" or "BHUTAN" or "SOMALIA" or "AFGHANISTAN" or "DEMOCRATIC REPUBLIC OF CONGO " or "ETHIOPIA" or "BURMA" or "CUBA" or "ERITREA")
then A_birthcountry = "Other";
run;

please help

but it comes with an error that it is invalid error

Re: top 10 frequency of one variable and "other" the rest

PaigeMiller — Sat, 30 Apr 2022 09:54:31 GMT

See https://blogs.sas.com/content/iml/2018/06/04/top-10-table-bar-chart.html

Also, this line is the error

If A_BirthCountry = not("MYANMAR (BURMA)" or "IRAQ" or "BHUTAN" or "SOMALIA" or "AFGHANISTAN" or "DEMOCRATIC REPUBLIC OF CONGO " 
or "ETHIOPIA" or "BURMA" or "CUBA" or "ERITREA")
then A_birthcountry = "Other";

It should say

If A_BirthCountry = not in ("MYANMAR (BURMA)" ,"IRAQ","BHUTAN",
"SOMALIA" ,"AFGHANISTAN","DEMOCRATIC REPUBLIC OF CONGO ",
"ETHIOPIA","BURMA" ,"CUBA","ERITREA") then A_birthcountry = "Other";

Please pay attention to details, not that DEMOCRATIC REPUBLIC OF CONGO has a space after it and before the double-quote, maybe that's not what you want.

From now on, please, when there are ERRORs in the log, then show the ENTIRE log for this DATA step. Do not show us parts of the log, as you have done, we need to see the ENTIRE log for this DATA step. Please do not say "but it comes with an error that it is invalid error" and tell us nothing further, this is non-informative, we need to see the ENTIRE log for this DATA step.

Re: top 10 frequency of one variable and "other" the rest

Cooksam13 — Mon, 02 May 2022 20:19:32 GMT

Thank you for your help and I tried your new code and this is the error I got

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

71:4

NOTE: Invalid numeric data, A_BirthCountry='IRAQ' , at line 71 column 4.

why does it come up as numeric/character?

Re: top 10 frequency of one variable and "other" the rest

PaigeMiller — Mon, 02 May 2022 21:15:56 GMT

We don't know what is on line 71. Show us the ENTIRE log for this DATA step, that's 100% of the log for this DATA step, every single line in the log for this data step. Please copy the log as text and then paste it into the window that appears when you click on the </> icon.