BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dunga
Obsidian | Level 7

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.

Income0>0-100>100-200>200-300>300-400>400-500>500-1000
COUNT1222111
PERCENT10202020101010

 

 

*/

 

 

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

1 REPLY 1
art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 781 views
  • 0 likes
  • 2 in conversation