How to create duplicate

[eyp500]

I have a table of 6 questions and another table of 300.000 IDs. Is there a way to join both tables without a key and create 1.800.000 entries as a result?

I am asking because for some reason on our table the rows for answers do not exist if there is no answer. I like to add these entries back in and make the answer as blank '.'

HAVE:

Id   Question   Answer   

1       Q1            20 

1       Q3            5    

2       Q1            10

2       Q2            5

2       Q3            15

.

.

.

WANT: 

Id   Question   Answer   

1       Q1            20 

1       Q2            .

1       Q3            5    

2       Q1            10

2       Q2            5

2       Q3            15

.

.

.

Thank you all in advance!

[Reeza]

Try this instead and let me know if it gets you closer. The SPARSE option will create a cross join type output, one for each level and add in the zeros. See the WANT data set.

proc freq data=want noprint;

table id*question / sparse out=want missing;

weight answer;

run;

Otherwise, you can do what's called a cross join and get it, but this should give you what you want. 

---

@eyp500 wrote:

I have a table of 6 questions and another table of 300.000 IDs. Is there a way to join both tables without a key and create 1.800.000 entries as a result?

I am asking because for some reason on our table the rows for answers do not exist if there is no answer. I like to add these entries back in and make the answer as blank '.'

HAVE:

Id   Question   Answer   

1       Q1            20 

1       Q3            5    

2       Q1            10

2       Q2            5

2       Q3            15

.

.

.

WANT: 

Id   Question   Answer   

1       Q1            20 

1       Q2            .

1       Q3            5    

2       Q1            10

2       Q2            5

2       Q3            15

.

.

.

Thank you all in advance!

---

[ballardw]

---

@eyp500 wrote:

I have a table of 6 questions and another table of 300.000 IDs. Is there a way to join both tables without a key and create 1.800.000 entries as a result?

I

---

You actually have TWO have data sets, what exactly does the table of 6 questions look like?

[Ksharp]

data have;
input Id   question $   Answer   ;
cards;
1       Q1            20 
1       Q3            5    
2       Q1            10
2       Q2            5
2       Q3            15
;
run;

proc sql;
select a.*,b.answer
 from (select * from (select distinct id from have),(select distinct question from have)) as a
 left join have as b on a.id=b.id and a.question=b.question;
quit;