Cross join within a Table

[DipeshGupta]

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..??

[ChrisBrooks]

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;

[DipeshGupta]

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

[ChrisBrooks]

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;