topic Re: How to merge data by date rows in SAS Programming

How to merge data by date rows

jonatan_velarde — Sat, 27 Jul 2019 16:19:06 GMT

HI there:

I have the following data, indicating one gas fixed in a surface:

data have;

input data wall gas_concentration;

cards;

05/19/2014 11 220

05/19/2014 12 213

05/19/2014 13 180

05/19/2014 15 451

;

At the same time, i have environmental data associated to this fixation, from 4 months before sample collection, ans now i need toputgas concentration collected to each day from 4 months ago, like this:

date wal concentration temperature

01/19/2014 11 220 32

01/20/2014 11 220 33

01/21/2014 11 220 30

01/22/2014 11 220 28

01/23/2014 11 220 33

01/24/2014 11 220 32

05/19/2014 11 220 25

And the same for other walls where the samples were collected in the corresponding dates.

Thank you

Re: How to merge data by date rows

Tom — Sat, 27 Jul 2019 16:31:26 GMT

You didn't show the structure for your temperature dataset. But assuming that it has DATE and WALL in addition to TEMPERATURE then the you should be able to use SQL to join on WALL and when the date for temperature falls within the interval you wanted.

data have;
  input date :mmddyy. wall gas_concentration;
  format date yymmdd10.;
cards;
05/19/2014 11 220
05/19/2014 12 213
05/19/2014 13 180
05/19/2014 15 451
;

data temp;
  input date :mmddyy. wall temperature ;
  format date yymmdd10.;
cards;
01/18/2014 11  30
01/19/2014 11  32
01/20/2014 11  33
01/21/2014 11  30
05/19/2014 11  31
05/20/2014 11  32
;

proc sql ;
 create table want as 
   select a.date,a.wall,b.gas_concentration,a.temperature 
   from temp a
   inner join have b
   on a.wall = b.wall
    and a.date between intnx('month',b.date,-4,'same') and b.date
   order by 1,2
 ;
quit;

proc print;
run;

Obs          date    wall    gas_concentration    temperature

 1     2014-01-19     11            220                32
 2     2014-01-20     11            220                33
 3     2014-01-21     11            220                30
 4     2014-05-19     11            220                31

Re: How to merge data by date rows

jonatan_velarde — Sat, 27 Jul 2019 18:33:26 GMT

Nice program my friend

how could it be having this:

data have;
  input date :mmddyy. wall gas_concentration;
  format date yymmdd10.;
cards;
05/19/2014 11 220
05/19/2014 12 213
05/19/2014 13 180
05/19/2014 15 451
;

data temp;
  input date :mmddyy. temperature ;
  format date yymmdd10.;
cards;
01/18/2014  30
01/19/2014  32
01/20/2014  33
01/21/2014  30
05/19/2014  31
05/20/2014  32
;

With no wall information in data temp

Thanks in advance

Re: How to merge data by date rows

Tom — Sun, 28 Jul 2019 16:49:41 GMT

Just remove the condition that requires the WALL values to match from the ON clause.