BookmarkSubscribeRSS Feed
sathishthangamani
Calcite | Level 5


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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sathishthangamani
Calcite | Level 5

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Haikuo
Onyx | Level 15

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

Astounding
PROC Star

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?

sathishthangamani
Calcite | Level 5

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.

Astounding
PROC Star

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

SUM summ;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1193 views
  • 0 likes
  • 4 in conversation