topic How to create duplicate in SAS Programming

How to create duplicate

eyp500 — Thu, 31 Jan 2019 16:27:32 GMT

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!

Re: How to create duplicate

Reeza — Thu, 31 Jan 2019 16:33:56 GMT

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!

Re: How to create duplicate

ballardw — Thu, 31 Jan 2019 16:36:12 GMT

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

Re: How to create duplicate

Ksharp — Fri, 01 Feb 2019 14:34:12 GMT

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;