DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
Solution
‎11-13-2015 12:32 PM
Super User
Super User
Posts: 7,401

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

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. 

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

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

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;
New Contributor
Posts: 3

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

[ Edited ]

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:MMSmiley FrustratedS) 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!

Solution
‎11-13-2015 12:32 PM
Super User
Super User
Posts: 7,401

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

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. 

New Contributor
Posts: 3

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 175 views
  • 1 like
  • 2 in conversation