## How to list the values by range?

Occasional Contributor
Posts: 10

# How to list the values by range?

Hi,

I am trying to produce a report with a range diplay of multiple volumes.

Example:

input data:

d1 2

d2 1

d3 5

d4 8

d9 12

d10 1

d11 4

Output required:

Range Sum

d1-d3 16

d9-d11 17

-----

33

-----

Any insight or suggestion of which PROC to use is appreciated.

Thank you,

Sathish

Super User
Posts: 9,599

## Re: How to list the values by range?

What is the criterion to be a range in your example, for instance why is d4 not used and yet result includes d4 (to make 16)?

Once you identify your groups then assign a new variable e.g;

data want;

set have;

attrib range format=\$20.;

if 1 <=  input(tranwrd(d_var,"d",""),best.)  <= 4 then range="d1-d4";

if 5 <=  input(tranwrd(d_var,"d",""),best.)  <= 8 then range="d5-d5";

...

run;

However there are other ways depending on the data used and the required outcome, so please provide examples.

Occasional Contributor
Posts: 10

## Re: How to list the values by range?

My range is not fixed and varies, there will be 10000+ lines and cannot find range for each. So a generic range assignment would be a good idea. I do not know how to do it.

Here is my data:

 Start End Sum A26343 A26345 407.6985 A26349 A26350 625.9836 A26352 A26352 695.4059 A26355 A26357 16.02786 A26367 A26367 0.001389 A26372 A26375 705.0316
Super User
Posts: 9,599

## Re: How to list the values by range?

Sorry, I do not know what Axxxx for start and end are, or how they would be grouped into ranges?

Posts: 3,167

## Re: How to list the values by range?

From what I can see, you will need two steps, first to separate the serial numbers and form groups, next to summarize and concatenate.

data have;

infile cards ;

input var :\$ amt;

var_d=compress(var,,'kd');

group+(var_d-lag(var_d) ne 1);

summ+(-summ*(lag(group) ne group))+amt;

cards;

d1 2

d2 1

d3 5

d4 8

d9 12

d10 1

d11 4

;

data want;

retain var_range 'dddddddd';

set have;

by group;

if first.group then var_range=var;

if last.group then do;

var_range=catx('-',var_range,var);

output;

end;

keep var_range summ;

run;

Haikuo

Super User
Posts: 6,782

## Re: How to list the values by range?

There is no PROC that has the ability to read your mind.  You have to decide the method to form groups.  Do you want to take every 4 observations and collapse them into one group?  Do you want to sum the amounts until they reach a certain level to define a group?  Do you want 10 groups, using a relatively even split of the original observations?  Just what are you thinking is the definition of a group?

Occasional Contributor
Posts: 10

## Re: How to list the values by range?

Thanks Hai.Kuo for you code, i will try and let you know if i am successful.

Astounding, Basically my requirement is to group all the variables continiue together, if there is a gap in observation then needs to form a next group.

Super User
Posts: 6,782