Hi SAS Forum,
/*I am having this dataset with a single variable called income and the dataset has no Ids.
I need to categorize the income variable into bands.
I achieved it using the proc format.
Then Proc freq below generates a dataset called want_1.
Q: I wanted to get the want_1 dataset rotated so that the final answer dataset should be like below
(but without using proc tabulate the finally rotated answer dataset should be generated).
Could anyone help me?
Thanks
This should be the answer dataset.
Income 0 >0-100 >100-200 >200-300 >300-400 >400-500 >500-1000
COUNT 1 2 2 2 1 1 1
PERCENT 10 20 20 20 10 10 1 0
Same answer table is repeated below.
Income | 0 | >0-100 | >100-200 | >200-300 | >300-400 | >400-500 | >500-1000 |
COUNT | 1 | 2 | 2 | 2 | 1 | 1 | 1 |
PERCENT | 10 | 20 | 20 | 20 | 10 | 10 | 10 |
*/
data have;
input Income;
cards;
0
100
105
100
200
300
300
400
500
1000
;
run;
proc format ;
value val .= 'Missing'
low-<0 = '<0'
0 = '0'
0<-100 = '>0-100'
100<-200 = '>100-200'
200<-300 = '>200-300'
300<-400 = '>300-400'
400<-500 = '>400-500'
500<-1000 = '>500-1000'
1000<-high = '>1000';
run;
proc freq data=have noprint;
tables Income/list missing out=want_1 sparse; /*SPARSE option includes zero cell frequencies in the output
data set;*/
format Income val.;
run;
You set missing to the string Missing, but didn't include that in your desired output table.
If you really don't want to include missing values, the following code can be shortened because one would only need to use the formatted income values:
data have; input Income; cards; 0 100 105 100 200 300 300 400 500 1000 ; run; proc format ; value val .= 'Missing' low-<0 = '<0' 0 = '0' 0<-100 = '>0-100' 100<-200 = '>100-200' 200<-300 = '>200-300' 300<-400 = '>300-400' 400<-500 = '>400-500' 500<-1000 = '>500-1000' 1000<-high = '>1000'; run; proc summary data=have nway completetypes; class income / preloadfmt order=data missing; format income val.; output out=counts; run; data counts (drop=_income); set counts (rename=(income=_income)); income=put(_income,val.); run; proc freq data=counts order=data noprint; tables income / out=need sparse; weight _freq_ / zeros; run; options validvarname=any; proc transpose data=need out=want1 (drop=_label_) name=Income; var count percent; id income; run;
Art, CEO, AnalystFinder.com
You set missing to the string Missing, but didn't include that in your desired output table.
If you really don't want to include missing values, the following code can be shortened because one would only need to use the formatted income values:
data have; input Income; cards; 0 100 105 100 200 300 300 400 500 1000 ; run; proc format ; value val .= 'Missing' low-<0 = '<0' 0 = '0' 0<-100 = '>0-100' 100<-200 = '>100-200' 200<-300 = '>200-300' 300<-400 = '>300-400' 400<-500 = '>400-500' 500<-1000 = '>500-1000' 1000<-high = '>1000'; run; proc summary data=have nway completetypes; class income / preloadfmt order=data missing; format income val.; output out=counts; run; data counts (drop=_income); set counts (rename=(income=_income)); income=put(_income,val.); run; proc freq data=counts order=data noprint; tables income / out=need sparse; weight _freq_ / zeros; run; options validvarname=any; proc transpose data=need out=want1 (drop=_label_) name=Income; var count percent; id income; run;
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.