Match ID to list of months to get record for all months (SQL Cartesian)

[wcp_fnfg]

Looking to create a table which contains ID records for each possible months (table B).  Any thoughts?  I think this is a short SQL cartesian join, but I can't get the syntax clean.

data a;

input id $ month loss;

datalines;

123 201302 5

123 201306 14

;

run;

data b;

input month;

datalines;

201301

201302

201303

201304

201305

201306

201307

201308

201309

201310

201311

201312

;

run;

[Reeza]

I think this is what you're looking for

proc sql;

create table want as

select * from
(select distinct id from a

cross join

select distinct month from b

) as cross_joined

order by id, month;

quit;

[wcp_fnfg]

3724  proc sql;

3725  create table want as

3726  select * from

3727  (select distinct id from a

3728  cross join

3729  select distinct month from b

                      -----

                      22

                      76

ERROR 22-322: Syntax error, expecting one of the following: ), ',', ANSIMISS,

              CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT,

              NATURAL, NOMISS, OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

3730  ) as cross_joined

3731  order by id, month;

3732  quit;

[Reeza]

proc sql;

create table want as

select id, month from

(select distinct id as ID from a) as a

cross join

(select distinct month as Month from b) as b

order by id, month;

quit;

[wcp_fnfg]

That got me the list of ID and month, but not the loss.  I could merge this table back to the starting one though.     

[Reeza]

You could nest it in, but I find a second query is easier to read/debug.

You also didn't say you wanted that

proc sql;

*create table of all months by all IDs;

create table want as

select id, month from

(select distinct id as ID from a) as a

cross join

(select distinct month as Month from b) as b

order by id, month;

*Add in loss values to table;

create table want2 as

select a.*, b.loss

from want as a

LEFT JOIN A as b

on a.id=b.id

and a.month=b.month;

quit;

[data_null__]

I would make the date variable a SAS date.  You might consider PROC SUMMARY for expanding your data.

data a;
   input id $ month:yymmn. loss;
   format month yymmn.;
   datalines;
123 201302 5
123 201306 14
223 201304 15
223 201309 1
;;;;
   run;

data b;
   input month :yymmn.;
   format month yymmn.;
   datalines;
201301
201302
201303
201304
201305
201306
201307
201308
201309
201310
201311
201312
;;;;
   run;

proc summary data=a classdata=b nway;
   by id;
   class month;
   output out=expand(drop=_:) idgroup(out(loss)=);
   run;
proc print;
   run;

[wcp_fnfg]

Throwing this in the mix, uses summary statistic only to get rid of duplicates.

proc sql;

create table c as select

id,

b.month,

max(case when a.month = b.month then loss else . end) as loss

from a, b

group by id, b.month;

quit;