BookmarkSubscribeRSS Feed
wcp_fnfg
Obsidian | Level 7

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;

7 REPLIES 7
Reeza
Super User

I think this is what you're looking for Smiley Happy

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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

You also didn't say you wanted that Smiley Wink

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__
Jade | Level 19

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
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 830 views
  • 3 likes
  • 3 in conversation