CODEWITHSUNDEEP
sqlsasleft-joinproc
eternity1
eternity1

Reputation: 681

SAS - PROC SQL: two tables: each one column distinct value, left join

I have a table with distinct dates YYYYMMDD from 20000101 until 20001231 and a table with distinct time points (HH:MM:SS) from 09:30:00 until 16:00:00.

I would like to create a (left) join where every day gets repeated 391 times assigned with each time point. That looks to me like a left join, however, I do not have any id's for joining.

date          time
20000101    09:30:00
20000101    09:31:00
20000101    ...
20000101    ...
20000101    15:59:00
20000101    16:00:00
20000102    09:30:00
20000102    ...
20000102    16:00:00

how would the respective code look like (if there is no explicit common primary key to join on)?

PROC SQL;
   SELECT DISTINCT a.date, b.time
   FROM table_1 a, table_1 b (both information are in the same table)
   ;
QUIT;

Just as background: there are days that are "shorter" / less than 391 observation points. However, I would like to make sure every day has 391 observation points, just filled up with missing values.

Upvotes: 2

Views: 3098

Answers (3)

Richard
Richard

Reputation: 27498

To show all observed times (over all dates) for each date, as well as maintaining original satellite information I would use a reflexive cross join of the combinatoric columns for the basis of a reflexive left join.

Consider this sample data generator. It simulates the case of data being gathered at different intervals (every 10 or 20 minutes) on different days.

data have;
  do i = 1 to 5;
    date = '01-apr-2018'd + (i-1);
    do j = 0 to 4;
      time = '12:00't + (mod(i,2)+1) * 600 * j;  * every other day sample at 1o or 20 minute interval;
      x = ceil ( 25 * ranuni(123) );
      OUTPUT;
    end;
  end;
  format date yymmdd10. time time8.;
  keep date time x;
run;

SQl is used to cross join the distinct dates and times and then the original data is left joined to the cross join.

proc sql;
  create table cross_as_left_basis 
  as
  select 
    cross.date
  , cross.time
  , have.x
  from
    ( select distinct dates.date, times.time
      from       have as dates
      cross join have as times
    ) as
    cross
  left join 
    have 
  on 
    cross.date = have.date
    and cross.time = have.time
  ;

Have is

      date        time     x

2018-04-01    12:00:00    19
              12:20:00     9
              12:40:00     5
              13:00:00    23
              13:20:00     9

2018-04-02    12:00:00     6
              12:10:00    20
              12:20:00    10
              12:30:00     4
              12:40:00     5

2018-04-03    12:00:00    20
              12:20:00    11
              12:40:00    25
              13:00:00     7
              13:20:00    18

2018-04-04    12:00:00    14
              12:10:00    14
              12:20:00    22
              12:30:00     4
              12:40:00    22

2018-04-05    12:00:00    17
              12:20:00    20
              12:40:00    18
              13:00:00     9
              13:20:00    14

The join result is

      date        time     x

2018-04-01    12:00:00    19
              12:10:00     .
              12:20:00     9
              12:30:00     .
              12:40:00     5
              13:00:00    23
              13:20:00     9

2018-04-02    12:00:00     6
              12:10:00    20
              12:20:00    10
              12:30:00     4
              12:40:00     5
              13:00:00     .
              13:20:00     .

2018-04-03    12:00:00    20
              12:10:00     .
              12:20:00    11
              12:30:00     .
              12:40:00    25
              13:00:00     7
              13:20:00    18

2018-04-04    12:00:00    14
              12:10:00    14
              12:20:00    22
              12:30:00     4
              12:40:00    22
              13:00:00     .
              13:20:00     .

2018-04-05    12:00:00    17
              12:10:00     .
              12:20:00    20
              12:30:00     .
              12:40:00    18
              13:00:00     9
              13:20:00    14

Upvotes: 1

momo1644
momo1644

Reputation: 1804

You can do either a Left Join or Join and add Where 1=1 this will create the Cartesian Product for you:

Code:

proc sql;
 create table want as
  select t1.date, t2.time
  from t1 left join t2 on 1=1
  order by date, time;
quit;

Upvotes: 2

Nishant Gupta
Nishant Gupta

Reputation: 3656

You need Cartesian Product since you want to generate all combinations of date and time. So to produce such result you need CROSS JOIN in which you don't have to give any JOIN Condition.

Try the below query:

PROC SQL;
   SELECT a.date, b.time
   FROM table_1 a
   CROSS JOIN
   table_1 b
   GROUP BY a.date, b.time 
   ;
QUIT;

OR

PROC SQL;
       SELECT a.date, b.time
       FROM (SELECT date FROM table_1) a
       CROSS JOIN
       (SELECT time FROM table_1) b
       GROUP BY a.date, b.time 
       ;
QUIT;

For more info on CROSS JOIN Follow the below link:

http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#p1q7agzgxs9ik5n1p7k3sdft0u9u.htm

Upvotes: 2

Related Questions