The following code proposes a different layout for the result. Assuming you want the result in an Excel I'd consider such a layout as easier to work with because you can easily filter by rank to determine which language within a postcode is spoken the most, 2nd and so on frequently.
/* create a source .csv file in SAS WORK */
filename src_csv "%sysfunc(pathname(work))/source_data.csv";
data _null_;
file src_csv;
infile datalines truncover;
input;
put _infile_;
datalines;
Language,90001,90002,90003
English,35010.55,362,1201.05
French,1250.65,7805.23,.
Spanish,2951,.,4835.702
Chinese,130.25,520103.07,.
Japanese,762.79,6945.33,1234.693
Russian,501.86,7485.45,9210.252
;
run;
/* import .csv into a SAS table */
%let sv_validvarname=%sysfunc(getoption(validvarname,keyword));
options validvarname=any;
proc import
datafile="%sysfunc(pathname(work))/source_data.csv"
dbms=csv
out=work.have
replace;
run;
options &sv_validvarname;
/* transpose to long data structure */
proc transpose data=work.have out=work.have_long(rename=(_name_=zip_code col1=n_people)) ;
by Language notsorted;
run;
proc sort data=work.have_long out=work.have_long_sorted;
by zip_code n_people Language;
run;
proc rank data=work.have_long_sorted out=work.want_ranked ties=dense descending;
by zip_code;
ranks rank;
var n_people;
run;
proc sort data=work.want_ranked;
by zip_code rank;
run;
proc print data=work.want_ranked;
run;
It would also not be hard to exclude the rows with missings or alternatively set the values to zero and/or ensure that for each zip-code all languages get listed.
... View more
