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.
