Solved
Highlighted
Contributor
Posts: 38

# Horizontal rotation problem?

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;

Accepted Solutions
Solution
‎08-12-2017 06:30 PM
PROC Star
Posts: 7,471

## Re: Horizontal rotation problem?

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

All Replies
Solution
‎08-12-2017 06:30 PM
PROC Star
Posts: 7,471

## Re: Horizontal rotation problem?

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

☑ This topic is solved.