Join two tables with no common field

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Join two tables with no common field

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

NOMEIDADESEXOQTDE
WILLIAM30M2
MARCOS31M1
CAMILO54M3
CRISTINA50F4
WESLEY38M1


Table B

SKVALORFLAG
130S
250N
322S
415S
578N
630N
714N
896S
9157N
1012S
1139N


I would get this result:


NOMEIDADESEXOVALORFLAG
WILLIAM30M30S
WILLIAM30M50N
MARCOS31M22S
CAMILO54M15S
CAMILO54M78N
CAMILO54M30N
CRISTINA50F14N
CRITSINA50F96S
CRISTINA50F157N
CRISTINA50F12S
WESLEY38M39N


tks.



Accepted Solutions
Solution
‎03-13-2014 02:43 PM
Contributor
Posts: 21

Re: Join two tables with no common field

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,498

Re: Join two tables with no common field

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

Contributor
Posts: 21

Re: Join two tables with no common field

Tom,

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

Super User
Super User
Posts: 6,498

Re: Join two tables with no common field

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

Contributor
Posts: 21

Re: Join two tables with no common field

ok Tom! veryTks....

Super User
Posts: 5,079

Re: Join two tables with no common field

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.

Solution
‎03-13-2014 02:43 PM
Contributor
Posts: 21

Re: Join two tables with no common field

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;

Super User
Posts: 5,079

Re: Join two tables with no common field

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?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 350 views
  • 0 likes
  • 3 in conversation