I have a big table in SAS where columns represent all the countries in the world. I have created it through long-to-wide transpose and I am representing a value for each ID in each respective country - see the code below:
proc transpose data=basic_summarised out=cntrs_transposed (drop=_name_ _label_);
by ID code;
id country;
var value;
run;
My goal now is to re-arrange the columns so that the country columns appear in an output alphabetically. To achieve this, I run a macro:
%macro reorder_vars(Dataset);
proc sql noprint;
select compress(name,,'kn') into : dsn separated by " "
from dictionary.columns
where libname="%upcase(%scan(&dataset,1,.))" and memname="%upcase(%scan(&dataset,2,.))"
order by name
;
quit;
data %scan(&dataset,2,.);
retain ID code &dsn;
set &Dataset;
run;
%mend;
%reorder_vars(work.cntrs_transposed);
This works but only on countries with one-word names, those with punctuation marks or spaces end up at the very end of the variable list (examples of those countries are United States, Congo (Republic), etc.). I tried changing the compress modifiers to 'ps', thinking that it would get rid of punctuation and spaces but the table remains unchanged.
I am not sure if there is anything else here I could do to achieve the desired result?
Any help would be much appreciated, thank you!
How is the name for the United States column in the data set? Is it 'United States'n or is it United_States?
The same notation needs to be used for the RETAIN statement.
NLITERAL can be used if the name has spaces.
proc sql noprint;
select nliteral(name) into : dsn separated by " "
from dictionary.columns
where libname="%upcase(%scan(&dataset,1,.))" and memname="%upcase(%scan(&dataset,2,.))"
order by name
;
quit;