So I saved the Excel to a csv file and copied that to my SAS server. It's much easier to get the right types and contents if you don't force SAS to make guesses (what you have to do with the xlsx format). On top of that, I don't have ACCESS to PC Files licensed.
Excel used a semicolon as delimiter (although csv explicitly means comma seperated volume), so I had to adapt my input data step.
data in (keep=sex category ny kc);
infile
'$HOME/sascommunity/nyfile.csv' /* filename on my server */
dlm=';' /* use semicolon as delimiter */
dsd /* in case a delimiter or an empty cell appears somewhere */
firstobs=4 /* start in line 4, everything before that is irrelevant */
;
length /* set lengths for the variables needed */
sex $6
category $50
ny 8
var3 $10 /* var3 is just a dummy, as we only need the population estimates */
kc 8
;
format ny kc comma12.; /* Nice format for the numbers */
label /* Nice names for the csv file */
sex = 'Sex'
category = 'Category'
ny = 'Pop. Estimate New York'
kc = 'Pop. Estimate Kings County'
;
retain sex '' flag 0; /* keep these variables across observations */
input
category
ny : comma12.
var3
kc : comma12.
;
/* determine the sex: */
if category in ('Male:','Female:') then sex = scan(category,1,':');
/* check the category fulltime or not: */
if substr(category,1,6) = 'Worked' then flag = 1;
if substr(category,1,5) = 'Other' then flag = 0;
if flag and category in ( /* only if we are in the right category and sub-category */
'$55,000 to $64,999',
'$65,000 to $74,999',
'$75,000 to $99,999',
'$100,000 or more'
); /* this is called a subsetting if */
run;
/* check the dataset */
proc print data=in noobs;
run;
/* the easiest part: create the csv */
proc export data=in file='$HOME/sascommunity/want.csv' dbms=csv replace label;
run;
Output of the proc print:
sex category ny kc
Male $55,000 to $64,999 244,273 16,330
Male $65,000 to $74,999 199,338 14,708
Male $75,000 to $99,999 311,572 25,495
Male $100,000 or more 462,618 59,399
Female $55,000 to $64,999 176,444 13,596
Female $65,000 to $74,999 130,768 11,639
Female $75,000 to $99,999 184,576 18,605
Female $100,000 or more 180,911 29,356
The csv file:
"Sex","Category","Pop. Estimate New York","Pop. Estimate Kings County"
Male,"$55,000 to $64,999","244,273","16,330"
Male,"$65,000 to $74,999","199,338","14,708"
Male,"$75,000 to $99,999","311,572","25,495"
Male,"$100,000 or more","462,618","59,399"
Female,"$55,000 to $64,999","176,444","13,596"
Female,"$65,000 to $74,999","130,768","11,639"
Female,"$75,000 to $99,999","184,576","18,605"
Female,"$100,000 or more","180,911","29,356"
