[SOLVED] Run a code while stratifying by two variables – Stack Overflow

Issue

This Content is from Stack Overflow. Question asked by Elb

suppose to have the following simple case (only for explanatory purposes. Original data are more complicated to show):

    data have;
    input ID :$20. Label1 :$20. Label2 :$20. Hours :$20.;
    cards;
    0001    rep1   w  345 
    0001    rep1   f  985     
    0001    rep1   w  367
    0001    rep2   w  65
    0001    rep2   w  123
    0001    rep2   f  120
    0002    rep6   f  45
    0002    rep6   w  657
    0002    rep6   w  45
    0002    rep1   w  567
    0002    rep1   f  78
    0002    rep1   w  9     
    .....   ....   ... ...
;

I would like to sum, foreach ID the hours corresponding to “w” but also stratifying by Label1, i.e. rep*. I used:

  data want;
  set have;
  by ID Label1;
      if first.ID 
      ..........
      if last.ID
      .........
  run;

Although I was able to stratify by ID I was not able to stratify by Label1.
Is it possible to write as follows: if first.ID and first.Label1 then….?
While doing some attempts, SAS gave me also the following error:
“by variables are not properly sorted on data set have”. Input data are sorted by ID.

Thank you in advance



Solution

Obviously and as you said, the input data is sorted by ID, so you can use first.ID. But the data is not sorted by label, therefore you cannot use first.label. If you want to use both you have to sort by both variables:

 proc sort data=have;
   by ID label;
 quit;

But keep in mind that in your sample data there will then be not only one first.label=1 for label=rep1, but twice:

                        first.ID     first.label
0001    rep1   w  345      1           1
0001    rep1   f  985      0           0
0001    rep1   w  367      0           0
0001    rep2   w  65       0           1
0001    rep2   w  123      0           0
0001    rep2   f  120      0           0
0002    rep1   w  567      1           1
0002    rep1   f  78       0           0
0002    rep1   w  9        0           0
0002    rep6   f  45       0           1
0002    rep6   w  657      0           0
0002    rep6   w  45       0           0


This Question was asked in StackOverflow by Elb and Answered by gregor It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?