topic Re: Cross join within a Table in SAS Enterprise Guide

Cross join within a Table

DipeshGupta — Fri, 08 Sep 2017 07:20:14 GMT

A 1

A 2

A 3

B 1

B 2
B 3
C 1

C 2
C 3

D 1

I have a data in this format where the values of row is not fixed and can take any value but at the end I want them in the order where for each value of column A there is all the possible values that column B can take i.e. 1 2 3 and if a column like that of D doesn't have sufficient values of B then I want to add tow additional values for D.

How to do that..??

Is there a way to apply cross join within a single table and if yes will that help..??

Re: Cross join within a Table

ChrisBrooks — Fri, 08 Sep 2017 08:12:07 GMT

You can do this with Proc Freq - it might be more efficient than an explicit Cross Join on a very large table.

data have;
	length letter $1;
	infile datalines;
	input letter $ numb;
	datalines;
A      1
A      2
A      3
B      1
B      2
B      3
C      1
C      2
C      3
D      1
;
run;

proc freq data=have noprint;
tables letter*numb/sparse out=want(drop=count percent);
run;

Re: Cross join within a Table

DipeshGupta — Fri, 08 Sep 2017 08:46:06 GMT

@ChrisBrooks Is is possible with proc tabulate also. I mean is there any function like sparse available with Proc Tabulate also..??

Re: Cross join within a Table

ChrisBrooks — Fri, 08 Sep 2017 08:52:36 GMT

Printmiss would be the equivalent in Proc Tabulate

proc tabulate data=have out=want2(keep=letter numb);
class letter numb;
tables letter*numb / printmiss;
run;