topic Re: Match ID to list of months to get record for all months (SQL Cartesian) in SAS Programming

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

wcp_fnfg — Tue, 01 Apr 2014 15:17:25 GMT

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;

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

Reeza — Tue, 01 Apr 2014 15:20:34 GMT

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;

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

wcp_fnfg — Tue, 01 Apr 2014 15:24:32 GMT

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

-----

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;

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

Reeza — Tue, 01 Apr 2014 15:33:55 GMT

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;

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

wcp_fnfg — Tue, 01 Apr 2014 15:35:56 GMT

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

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

Reeza — Tue, 01 Apr 2014 15:43:15 GMT

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;

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

data_null__ — Tue, 01 Apr 2014 15:49:00 GMT

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;

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

wcp_fnfg — Tue, 01 Apr 2014 15:56:40 GMT

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;