topic Assigning time-tied variable data to the previous minute's seconds in SAS in SAS Programming

Assigning time-tied variable data to the previous minute's seconds in SAS

Dukestep — Fri, 13 Nov 2015 15:25:54 GMT

I'm trying to figure out how to do the following in SAS. I have data taken by an NO2 sensor every minute. A GPS is recording a location at give or take every second. I need to assign the value of each data recording for every minute to the previous seconds of that past minute. The NO2 data recorded is an average of the previous minute. Here's a sample of my data.

| ID | LAT        | LONG        | DateTime         | No2 (mgm3) | Humidité (en %) | Température (celsisus) |
|----|------------|-------------|------------------|------------|-----------------|------------------------|
| 1  | 45.481746N | 073.571211W | 28SEP15:07:21:26 |            |                 |                        |
| 1  | 45.481681N | 073.571095W | 28SEP15:07:21:29 |            |                 |                        |
| 1  | 45.481661N | 073.571056W | 28SEP15:07:21:30 |            |                 |                        |
| 1  | 45.481619N | 073.570981W | 28SEP15:07:21:32 |            |                 |                        |
| 1  | 45.481538N | 073.570833W | 28SEP15:07:21:36 |            |                 |                        |
| 1  | 45.481518N | 073.570798W | 28SEP15:07:21:37 |            |                 |                        |
| 1  | 45.481498N | 073.570761W | 28SEP15:07:21:38 |            |                 |                        |
| 1  | 45.481478N | 073.570726W | 28SEP15:07:21:39 |            |                 |                        |
| 1  | 45.481458N | 073.570691W | 28SEP15:07:21:40 |            |                 |                        |
| 1  | 45.481439N | 073.570656W | 28SEP15:07:21:41 |            |                 |                        |
| 1  | 45.481419N | 073.570621W | 28SEP15:07:21:42 |            |                 |                        |
| 1  | 45.481401N | 073.570588W | 28SEP15:07:21:43 |            |                 |                        |
| 1  | 45.481383N | 073.570553W | 28SEP15:07:21:44 |            |                 |                        |
| 1  | 45.481364N | 073.570520W | 28SEP15:07:21:45 |            |                 |                        |
| 1  | 45.481346N | 073.570486W | 28SEP15:07:21:46 |            |                 |                        |
| 1  | 45.481328N | 073.570454W | 28SEP15:07:21:47 |            |                 |                        |
| 1  | 45.481309N | 073.570421W | 28SEP15:07:21:48 |            |                 |                        |
| 1  | 45.481291N | 073.570389W | 28SEP15:07:21:49 |            |                 |                        |
| 1  | 45.481275N | 073.570358W | 28SEP15:07:21:50 |            |                 |                        |
| 1  | 45.481256N | 073.570326W | 28SEP15:07:21:51 |            |                 |                        |
| 1  | 45.481239N | 073.570295W | 28SEP15:07:21:52 |            |                 |                        |
| 1  | 45.481223N | 073.570263W | 28SEP15:07:21:53 |            |                 |                        |
| 1  | 45.481204N | 073.570233W | 28SEP15:07:21:54 |            |                 |                        |
| 1  | 45.481188N | 073.570203W | 28SEP15:07:21:55 |            |                 |                        |
| 1  | 45.481171N | 073.570173W | 28SEP15:07:21:56 |            |                 |                        |
| 1  | 45.481156N | 073.570143W | 28SEP15:07:21:57 |            |                 |                        |
| 1  | 45.481140N | 073.570113W | 28SEP15:07:21:58 |            |                 |                        |
| 1  | 45.481123N | 073.570083W | 28SEP15:07:21:59 |            |                 |                        |
| 1  | 45.481108N | 073.570054W | 28SEP15:07:22:00 | 0.007      | 55.9            | 22.4                   |

I am looking to bring the data from the last line (NO2, Humidity, Temperature) "up" to seconds of the previous minute which have a GPS reading. The column is in DateTime format.

Would love any pointers on how to do this... Thanks in advance!

Re: Assigning time-tied variable data to the previous minute's seconds in SAS

RW9 — Fri, 13 Nov 2015 15:35:06 GMT

Am leaving for the day, so can't test this. But a merge of part of the data back to the main data should work:

proc sql;
  create table WANT as
  select  A.ID
             ,A.LAT
             ,A.LONG
             ,A.DATETIME
             ,B.NO2
             ,B.HUMIDITY
             ,B.TEMP
  from    WORK.HAVE A
  left join (select * from WORK.HAVE where NO2 is not null or HUMIDITY is not null or TEMP is not null) B
  on      A.ID=B.ID;
quit;

Re: Assigning time-tied variable data to the previous minute's seconds in SAS

Dukestep — Fri, 13 Nov 2015 16:17:41 GMT

Thanks @RW9.

This does indeed work for the data sample I posted above. However, I may have left out some key information.

What I'm trying to do is this : only affect an NO2 reading to the seconds of the previous minute, so that, for example, during a 10 minute I would have a different N02, HUMIDITY and TEMP reading for every minute which would then be passed on to the seconds of the previous minute a total of ten times. Here's some more sample data to make this a little more clear:

| ID | LAT        | LONG        | DateTime         | NO2   | HUMIDITY | TEMP |
|----|------------|-------------|------------------|-------|----------|------|
| 1  | 45.481746N | 073.571211W | 28SEP15:07:21:26 |       |          |      |
| 1  | 45.481681N | 073.571095W | 28SEP15:07:21:29 |       |          |      |
| 1  | 45.481661N | 073.571056W | 28SEP15:07:21:30 |       |          |      |
| 1  | 45.481619N | 073.570981W | 28SEP15:07:21:32 |       |          |      |
| 1  | 45.481538N | 073.570833W | 28SEP15:07:21:36 |       |          |      |
| 1  | 45.481518N | 073.570798W | 28SEP15:07:21:37 |       |          |      |
| 1  | 45.481498N | 073.570761W | 28SEP15:07:21:38 |       |          |      |
| 1  | 45.481478N | 073.570726W | 28SEP15:07:21:39 |       |          |      |
| 1  | 45.481458N | 073.570691W | 28SEP15:07:21:40 |       |          |      |
| 1  | 45.481439N | 073.570656W | 28SEP15:07:21:41 |       |          |      |
| 1  | 45.481419N | 073.570621W | 28SEP15:07:21:42 |       |          |      |
| 1  | 45.481401N | 073.570588W | 28SEP15:07:21:43 |       |          |      |
| 1  | 45.481383N | 073.570553W | 28SEP15:07:21:44 |       |          |      |
| 1  | 45.481364N | 073.570520W | 28SEP15:07:21:45 |       |          |      |
| 1  | 45.481346N | 073.570486W | 28SEP15:07:21:46 |       |          |      |
| 1  | 45.481328N | 073.570454W | 28SEP15:07:21:47 |       |          |      |
| 1  | 45.481309N | 073.570421W | 28SEP15:07:21:48 |       |          |      |
| 1  | 45.481291N | 073.570389W | 28SEP15:07:21:49 |       |          |      |
| 1  | 45.481275N | 073.570358W | 28SEP15:07:21:50 |       |          |      |
| 1  | 45.481256N | 073.570326W | 28SEP15:07:21:51 |       |          |      |
| 1  | 45.481239N | 073.570295W | 28SEP15:07:21:52 |       |          |      |
| 1  | 45.481223N | 073.570263W | 28SEP15:07:21:53 |       |          |      |
| 1  | 45.481204N | 073.570233W | 28SEP15:07:21:54 |       |          |      |
| 1  | 45.481188N | 073.570203W | 28SEP15:07:21:55 |       |          |      |
| 1  | 45.481171N | 073.570173W | 28SEP15:07:21:56 |       |          |      |
| 1  | 45.481156N | 073.570143W | 28SEP15:07:21:57 |       |          |      |
| 1  | 45.481140N | 073.570113W | 28SEP15:07:21:58 |       |          |      |
| 1  | 45.481123N | 073.570083W | 28SEP15:07:21:59 |       |          |      |
| 1  | 45.481108N | 073.570054W | 28SEP15:07:22:00 | 0.007 | 55.9     | 22.4 |
| 1  | 45.481091N | 073.570026W | 28SEP15:07:22:01 |       |          |      |
| 1  | 45.481076N | 073.569998W | 28SEP15:07:22:02 |       |          |      |
| 1  | 45.481059N | 073.569970W | 28SEP15:07:22:03 |       |          |      |
| 1  | 45.481044N | 073.569941W | 28SEP15:07:22:04 |       |          |      |
| 1  | 45.481030N | 073.569913W | 28SEP15:07:22:05 |       |          |      |
| 1  | 45.481014N | 073.569886W | 28SEP15:07:22:06 |       |          |      |
| 1  | 45.480999N | 073.569860W | 28SEP15:07:22:07 |       |          |      |
| 1  | 45.480985N | 073.569833W | 28SEP15:07:22:08 |       |          |      |
| 1  | 45.480969N | 073.569806W | 28SEP15:07:22:09 |       |          |      |
| 1  | 45.480956N | 073.569780W | 28SEP15:07:22:10 |       |          |      |
| 1  | 45.480941N | 073.569753W | 28SEP15:07:22:11 |       |          |      |
| 1  | 45.480928N | 073.569728W | 28SEP15:07:22:12 |       |          |      |
| 1  | 45.480913N | 073.569703W | 28SEP15:07:22:13 |       |          |      |
| 1  | 45.480900N | 073.569676W | 28SEP15:07:22:14 |       |          |      |
| 1  | 45.480886N | 073.569651W | 28SEP15:07:22:15 |       |          |      |
| 1  | 45.480871N | 073.569628W | 28SEP15:07:22:16 |       |          |      |
| 1  | 45.480858N | 073.569603W | 28SEP15:07:22:17 |       |          |      |
| 1  | 45.480845N | 073.569578W | 28SEP15:07:22:18 |       |          |      |
| 1  | 45.480831N | 073.569555W | 28SEP15:07:22:19 |       |          |      |
| 1  | 45.480818N | 073.569529W | 28SEP15:07:22:20 |       |          |      |
| 1  | 45.480804N | 073.569506W | 28SEP15:07:22:21 |       |          |      |
| 1  | 45.480793N | 073.569483W | 28SEP15:07:22:22 |       |          |      |
| 1  | 45.480779N | 073.569460W | 28SEP15:07:22:23 |       |          |      |
| 1  | 45.480766N | 073.569436W | 28SEP15:07:22:24 |       |          |      |
| 1  | 45.480755N | 073.569413W | 28SEP15:07:22:25 |       |          |      |
| 1  | 45.480741N | 073.569391W | 28SEP15:07:22:26 |       |          |      |
| 1  | 45.480729N | 073.569368W | 28SEP15:07:22:27 |       |          |      |
| 1  | 45.480718N | 073.569346W | 28SEP15:07:22:28 |       |          |      |
| 1  | 45.480706N | 073.569325W | 28SEP15:07:22:29 |       |          |      |
| 1  | 45.480693N | 073.569303W | 28SEP15:07:22:30 |       |          |      |
| 1  | 45.480681N | 073.569281W | 28SEP15:07:22:31 |       |          |      |
| 1  | 45.480669N | 073.569260W | 28SEP15:07:22:32 |       |          |      |
| 1  | 45.480658N | 073.569238W | 28SEP15:07:22:33 |       |          |      |
| 1  | 45.480646N | 073.569216W | 28SEP15:07:22:34 |       |          |      |
| 1  | 45.480636N | 073.569196W | 28SEP15:07:22:35 |       |          |      |
| 1  | 45.480624N | 073.569176W | 28SEP15:07:22:36 |       |          |      |
| 1  | 45.480613N | 073.569155W | 28SEP15:07:22:37 |       |          |      |
| 1  | 45.480601N | 073.569134W | 28SEP15:07:22:38 |       |          |      |
| 1  | 45.480591N | 073.569115W | 28SEP15:07:22:39 |       |          |      |
| 1  | 45.480579N | 073.569095W | 28SEP15:07:22:40 |       |          |      |
| 1  | 45.480570N | 073.569075W | 28SEP15:07:22:41 |       |          |      |
| 1  | 45.480558N | 073.569056W | 28SEP15:07:22:42 |       |          |      |
| 1  | 45.480548N | 073.569036W | 28SEP15:07:22:43 |       |          |      |
| 1  | 45.480536N | 073.569016W | 28SEP15:07:22:44 |       |          |      |
| 1  | 45.480526N | 073.568998W | 28SEP15:07:22:45 |       |          |      |
| 1  | 45.480515N | 073.568978W | 28SEP15:07:22:46 |       |          |      |
| 1  | 45.480504N | 073.568960W | 28SEP15:07:22:47 |       |          |      |
| 1  | 45.480494N | 073.568940W | 28SEP15:07:22:48 |       |          |      |
| 1  | 45.480483N | 073.568921W | 28SEP15:07:22:49 |       |          |      |
| 1  | 45.480473N | 073.568903W | 28SEP15:07:22:50 |       |          |      |
| 1  | 45.480463N | 073.568883W | 28SEP15:07:22:51 |       |          |      |
| 1  | 45.480453N | 073.568864W | 28SEP15:07:22:52 |       |          |      |
| 1  | 45.480443N | 073.568846W | 28SEP15:07:22:53 |       |          |      |
| 1  | 45.480246N | 073.568474W | 28SEP15:07:22:54 |       |          |      |
| 1  | 45.480086N | 073.568175W | 28SEP15:07:22:55 |       |          |      |
| 1  | 45.479948N | 073.567930W | 28SEP15:07:22:56 |       |          |      |
| 1  | 45.479838N | 073.567730W | 28SEP15:07:22:57 |       |          |      |
| 1  | 45.479751N | 073.567563W | 28SEP15:07:22:58 |       |          |      |
| 1  | 45.479685N | 073.567424W | 28SEP15:07:22:59 |       |          |      |
| 1  | 45.479631N | 073.567313W | 28SEP15:07:23:00 | 0.037 | 49.4     | 21.8 |

In this data I have data for NO2, HUMIDITY and TEMP at two different minutes (7:22 and 7:23). I need to move these "up" to the seconds of the previous minute. Logically, with your code, the readings from 7:22:00(HH:MM:SS) are also affected to the seconds that should have values from the readings at 7:23:00.

I know your leaving for the day but when you do have time, I would love to see if you could help me out a little more. Till then, I'll mess around some more. Much appreciated, by the way!

Re: Assigning time-tied variable data to the previous minute's seconds in SAS

RW9 — Fri, 13 Nov 2015 17:01:27 GMT

Well, guessing again here as not in front of computer. Required output would be useful.

proc sql;
  create table WANT as
  select  A.ID
             ,A.LAT
             ,A.LONG
             ,A.DATETIME
             ,B.NO2
             ,B.HUMIDITY
             ,B.TEMP
  from    WORK.HAVE A
  left join (select * from WORK.HAVE where NO2 is not null or HUMIDITY is not null or TEMP is not null) B
  on      A.ID=B.ID
  and     timepart(B.DATETIME) - "00:01"t <= timepart(A.DATETIME) <= timepart(B.DATETIME);
quit;

Its the "and" here which is key, and what you want to look at. What I am saying is merge on id, then if the main data's time part is within NO2 time - 1minute and NO2 time. Something like that should be near enough what you want.

Re: Assigning time-tied variable data to the previous minute's seconds in SAS

Dukestep — Fri, 13 Nov 2015 17:34:12 GMT

Thank you @RW9!

This has indeed worked as intended. I was toying with and AND before your reply but was struggling to write it to do what I wanted it to do. Much appreciated.