topic How to list the values by range? in SAS Procedures

How to list the values by range?

sathishthangamani — Thu, 29 May 2014 07:00:38 GMT

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

-----

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

Thank you,

Sathish

Re: How to list the values by range?

RW9 — Thu, 29 May 2014 08:27:58 GMT

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.

Re: How to list the values by range?

sathishthangamani — Thu, 29 May 2014 09:58:15 GMT

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

Re: How to list the values by range?

RW9 — Thu, 29 May 2014 10:04:44 GMT

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

Re: How to list the values by range?

Haikuo — Thu, 29 May 2014 12:51:28 GMT

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

Re: How to list the values by range?

Astounding — Thu, 29 May 2014 13:56:10 GMT

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?

Re: How to list the values by range?

sathishthangamani — Thu, 29 May 2014 14:07:28 GMT

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.

Re: How to list the values by range?

Astounding — Thu, 29 May 2014 14:13:57 GMT

In that case, Hai.Kuo definitely has the right idea. You just have to print his final data set, perhaps adding

SUM summ;