topic Re: Join two tables with no common field in SAS Programming

Join two tables with no common field

wrosario — Mon, 10 Mar 2014 13:20:13 GMT

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.

Re: Join two tables with no common field

Tom — Mon, 10 Mar 2014 13:38:39 GMT

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

Re: Join two tables with no common field

wrosario — Mon, 10 Mar 2014 13:55:07 GMT

Tom,

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

Re: Join two tables with no common field

Tom — Mon, 10 Mar 2014 15:03:44 GMT

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

Re: Join two tables with no common field

wrosario — Mon, 10 Mar 2014 16:41:07 GMT

ok Tom! veryTks....

Re: Join two tables with no common field

Astounding — Mon, 10 Mar 2014 16:47:26 GMT

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.

Re: Join two tables with no common field

wrosario — Thu, 13 Mar 2014 18:43:43 GMT

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;

while(QTD_EXP<QTDE);

QTD_EXP = QTD_EXP+

output;

end;

QTD_EXP=

run;

data

RESULTADO(drop =QTDE);

merge

TABELA_A_EXPANDIDA TABELA_B;

run;

Re: Join two tables with no common field

Astounding — Thu, 13 Mar 2014 19:09:35 GMT

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?