Desktop productivity for business analysts and programmers

Cross join within a Table

Reply
Contributor
Posts: 70

Cross join within a Table

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

Valued Guide
Posts: 596

Re: Cross join within a Table

[ Edited ]
Posted in reply to DipeshGupta

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;

 

 

 

Contributor
Posts: 70

Re: Cross join within a Table

Posted in reply to ChrisBrooks

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

Valued Guide
Posts: 596

Re: Cross join within a Table

Posted in reply to DipeshGupta

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;
Ask a Question
Discussion stats
  • 3 replies
  • 338 views
  • 0 likes
  • 2 in conversation