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

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!

1 ACCEPTED SOLUTION

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

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

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

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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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