cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dukestep
Calcite | Level 5 Dukestep
Calcite | Level 5
Go to Solution

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

Posted 11-13-2015 10:24 AM (1680 views)

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!

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Posted 11-13-2015 12:01 PM (1825 views)  |   In reply to Dukestep

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Posted 11-13-2015 10:35 AM (1670 views)  |   In reply to Dukestep

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;
0 Likes
Dukestep
Calcite | Level 5 Dukestep
Calcite | Level 5

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

Posted 11-13-2015 11:04 AM (1664 views)  |   In reply to RW9

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!

0 Likes
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Posted 11-13-2015 12:01 PM (1826 views)  |   In reply to Dukestep

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. 

Dukestep
Calcite | Level 5 Dukestep
Calcite | Level 5

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

Posted 11-13-2015 12:34 PM (1653 views)  |   In reply to RW9

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.

0 Likes

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • ‎11-13-2015 10:24 AM
  • 1681 views
  • 1 like
  • 2 in conversation