Join two tables with no common field

[wrosario]

Good morning,

I have two tables, and they do not have any field that the join can be done, the table has a field QTDE, indicating qtas lines should I take from table B, I wonder how can I do this in sas.

obs.I'm using the data integration studio, not want to use programming code if possible.

Table A

NOME	IDADE	SEXO	QTDE
WILLIAM	30	M	2
MARCOS	31	M	1
CAMILO	54	M	3
CRISTINA	50	F	4
WESLEY	38	M	1

Table B

SK	VALOR	FLAG
1	30	S
2	50	N
3	22	S
4	15	S
5	78	N
6	30	N
7	14	N
8	96	S
9	157	N
10	12	S
11	39	N

I would get this result:

NOME	IDADE	SEXO	VALOR	FLAG
WILLIAM	30	M	30	S
WILLIAM	30	M	50	N
MARCOS	31	M	22	S
CAMILO	54	M	15	S
CAMILO	54	M	78	N
CAMILO	54	M	30	N
CRISTINA	50	F	14	N
CRITSINA	50	F	96	S
CRISTINA	50	F	157	N
CRISTINA	50	F	12	S
WESLEY	38	M	39	N

tks.

[Tom]

Sounds like your join criteria is that SK <= QTDE.

[wrosario]

Tom,

this does not work, it will always go back and get that record do not match that name.

[Tom]

I do not use Data Integration Studio so I cannot help with how to get it do what you want.

[wrosario]

ok Tom! veryTks....

[Astounding]

Similarly, not a DI Studio user.  Here's how you could do it in a DATA step:

data want;

   set table_A;

   do i=1 to qdte;

      set table_B;

      output;

   end;

   keep nome idade sexo valor flag;

run;

SQL will not be an option.  It doesn't have a guaranteed order to the records, so your references to Table_B might not retrieve what you hope for when using SQL.

Good luck.

[wrosario · Accepted solution]

data

TABELA_A;

infile datalines delimiter=' ';

input SK NOME $ IDADE SEXO $ QTDE ;

datalines;

1 WILLIAM 30 M 2

2 MARCOS 31 M 1

3 CAMILO 54 M 3

4 CRISTINA 50 F 4

5 WESLEY 38 M 1

;

data

TABELA_B;

infile datalines delimiter=' ';

input SK VALOR FLAG $ ;

datalines;

1 30 S

2 50 N

3 22 S

4 15 S

5 78 N

6 30 N

7 14 N

8 96 S

9 157 N

10 12 S

11 39 N

;

　

data

TABELA_A_EXPANDIDA(drop=QTD_EXP );

set

TABELA_A;

retain QTD_EXP;

if _N_ = 1 then QTD_EXP=0;

do

while(QTD_EXP<QTDE);

QTD_EXP = QTD_EXP+

1;

output;

end;

QTD_EXP=

0;

run;

　

data

RESULTADO(drop =QTDE);

merge

TABELA_A_EXPANDIDA TABELA_B;

run;

[Astounding]

OK, I'm puzzled.  I can't picture a case where your program would work, but the solution that I posted would fail ... unless it's possible that QTDE could take on a value of zero.  Is that a possibility?