topic Re: addition data points in SAS Programming

addition data points

Joostvanerp — Wed, 04 Nov 2020 20:21:34 GMT

Hello everybody,

I can't figure out how to reach this; The interval value from my data ranges from 1 to 8. 1 starts at the beginning of each day.

When the interval value goes from 2 to 4, interval 3 is missing. I would like to make another line for this with the value of eat_sec, mcount and dmintake_g 0 and the value of the day the same as where the interval is supposed to be in.
Interval 1 is from 5:00:00 to 7:59:59 interval 2 from 8:00:00 to 10:59:59 etc. etc. interval 8 is 02:00:00 to 4:59:59.
In the new line, the starttime should be the same as the starttime of the interval it is in.

 starttime  day eat_sec dmintake_g    interval
 5:50:06      9  1120        100         1
 6:50:06      9	1544        150         1
 12:04:51     9	1261	    400         3
 18:20:41     9	2810	     42         5
 19:45:39     9	1158	    785         5
 22:15:21     9	1501	     33         6
 5:15:45     10	291	   1100	        1
 15:23:30    10	515         180         4
 18:15:44    10	2829	   1170	        5
 20:28:08    10	1318	    540         6
 22:05:39    10	1686	    690	        6
 9:22:37     11	2883	     12	        2
 11:05:39    11	1303	    100	        3
 14:46:54    11	947         300      	4
 17:13:04    11	2176	    100     	4
 19:49:31    11	1142	    450	        5
 21:05:01    11	1071        450	        6
 21:55:15    11	1306	    520	        6

Thank you in advance

Re: addition data points

ChrisNZ — Thu, 05 Nov 2020 05:32:58 GMT

Show us the output you seek.

Re: addition data points

Joostvanerp — Sun, 08 Nov 2020 19:39:26 GMT

The output I'm looking for is:

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

Re: addition data points

Joostvanerp — Fri, 13 Nov 2020 18:02:37 GMT

Is there a statement to create an additional data row if a value in a column is missing?

Re: addition data points

Kurt_Bremser — Sat, 14 Nov 2020 08:43:11 GMT

See this:

proc format;
invalue intstart
  '1' = '5:00:00't
  '2' = '8:00:00't
  '3' = '11:00:00't
  '4' = '14:00:00't
  '5' = '17:00:00't
  '6' = '20:00:00't
  '7' = '23:00:00't
  '8' = '2:00:00't
;
run;

data want;
merge
  have
  /* look-ahead: */
  have (
    firstobs=2
    keep=day interval
    rename=(day=_day interval=_int)
  )
;
output;
eat_sec = 0;
dmintake_g = 0;
do interval = interval + 1 to ifn(_int < interval,9,_int) - 1;
  starttime = input(put(interval,best.-l),intstart.);
  output;
end;
drop _int;
run;

Re: addition data points

Joostvanerp — Wed, 18 Nov 2020 20:46:41 GMT

I'm having some difficulties with

data want;
merge
  have
  /* look-ahead: */
  have (
    firstobs=2
    keep=day interval
    rename=(day=_day interval=_int)
  )
;

I do now have this, what goes wrong? I don't understand the want and having function

data lot.datafile;
merge
  have lot.datafile-lot.datafile
  /* look-ahead: */
  have (
    firstobs=2
    keep=day interval
    rename=(day=_day interval=_int)
  )
;

Re: addition data points

ChrisNZ — Wed, 18 Nov 2020 22:34:01 GMT

This type of question is exactly why I put all the keywords in lower case and the user words in uppercase.

data WANT;
merge
  HAVE
  /* look-ahead: */
  HAVE(
    firstobs=2
    keep    =DAY INTERVAL
    rename  =(DAY=_DAY INTERVAL=_INT)
  )
;

More legible to you? WANT and HAVE are not options. They are data set names

Re: addition data points

Kurt_Bremser — Thu, 19 Nov 2020 05:06:06 GMT

want and have are dataset names, used to make clear which is the data we have and which is the data we want to end up with.

Since you do not seem to be familiar with the most basic elements of the SAS language, I strongly recommend that you work through the free online Programming 1 course to get a grip on them.

In the meantime, the documentation of the DATA and MERGE statements and the Dataset Options will be helpful.