topic First statement in combination with sorting in SAS Programming

First statement in combination with sorting

Joostvanerp — Sat, 24 Oct 2020 18:52:11 GMT

Hi all,

In my data, I'm combining data rows with the same interval value. Since the interval is a specific time frame in a day, I do first sort the data for day and after that for starttime. But when I do so, the first statement doesn't work and the data is not combined. I think that it has to do something with the by statement in combination with the first and last statement.

proc sort data=lot.data2;
by day starttime; 

 run;


option locale=en_US;
data lot.data2intervals (drop=eat_sec dmintake_g starttime rename=(sumsec=Eat_sec sumdmintake=dmintake_g timegrp1=starttime));

length interval 8 dmintake_g 8 timegrp1 $48 mcount 8 day 8 eat_sec 8;
	set lot.data2;
		by day starttime interval;


	retain sumdmintake 0;
	retain timegrp1;
	retain sumsec 0;

	if first.interval then do;
		sumdmintake=0;
		timegrp1=put(timehours,nldatm48.);   *nldatm48.   ;
			sumsec=0;
	end;

	sumdmintake=sumdmintake+dmintake_g;
	sumsec=sumsec+eat_sec;
	
	
	if last.interval then do;
		output;
	end;


run;

mcount   starttime  day eat_sec dmintake_g    interval
8      	3:46:49      9	1171	    100        	1
1       6:50:06      9	1544        150         2
2	9:35:23	     9	1380         10         3
3	12:04:51     9	1261	    400         4
4	14:31:59     9	1175	    700         5
5	18:20:41     9	2810	     42         6
6	19:45:39     9	1158	    785         6
7	22:15:21     9	1501	     33         7
8	3:02:41	    10	906	    183         1
1	6:48:12	    10	1650	   6400	     	2
9	5:15:45     10	291	   1100	        2
2	9:51:50	    10	1598	   6100         3
3	11:07:00    10	963	   4000	        4
4	15:23:30    10	515         180         5
5	18:15:44    10	2829	   1170	        6
6	20:28:08    10	1318	    540         7
7	22:05:39    10	1686	    690	        7
1	9:22:37	    11	2883	     12	        3
2	11:05:39    11	1303	    100	        4
3	14:46:54    11	947         300      	5
4	17:13:04    11	2176	    100     	5
5	19:49:31    11	1142	    450	        6
6	21:05:01    11	1071        450	        7
7	21:55:15    11	1306	    520	        7

This is the data.

Re: First statement in combination with sorting

Kurt_Bremser — Sat, 24 Oct 2020 19:11:46 GMT

Sort by day interval starttime. Then you can combine the two 6's for day 9, and the 7's in the other days.

You must also use

by day interval;

in the data step.

Re: First statement in combination with sorting

Kurt_Bremser — Sat, 24 Oct 2020 21:48:41 GMT

For your better understanding:

The BY statement in a data step sets up a hierarchy, from left to right. A group change to the left automatically implies a group change further to the right.

So, the way you use it, all changes in starttime also force a change for interval, and a new first. there.

Re: First statement in combination with sorting

Joostvanerp — Sun, 25 Oct 2020 11:12:59 GMT

This works for the combining of the intervals, but I need to combine the intervals only if they are behind each other after it is sorted on starttime, not when it is sorted for the interval.

Re: First statement in combination with sorting

Kurt_Bremser — Sun, 25 Oct 2020 11:44:56 GMT

Then you can sort

by day starttime;

and use

by day interval notsorted;
if first.interval then ....;
if last.interval then ....;

in the data step.