Help using Base SAS procedures

How to list the values by range?

Reply
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
Super User
Posts: 7,997

Re: How to list the values by range?

Posted in reply to sathishthangamani

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 EndSum
A26343A26345407.6985
A26349A26350625.9836
A26352A26352695.4059
A26355A2635716.02786
A26367A263670.001389
A26372A26375705.0316
Super User
Super User
Posts: 7,997

Re: How to list the values by range?

Posted in reply to sathishthangamani

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

Respected Advisor
Posts: 3,156

Re: How to list the values by range?

Posted in reply to sathishthangamani

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: 5,518

Re: How to list the values by range?

Posted in reply to sathishthangamani

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?

Posted in reply to sathishthangamani

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: 5,518

Re: How to list the values by range?

Posted in reply to sathishthangamani

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

SUM summ;

Ask a Question
Discussion stats
  • 7 replies
  • 354 views
  • 0 likes
  • 4 in conversation