BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wrosario
Calcite | Level 5

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.


1 ACCEPTED SOLUTION

Accepted Solutions
wrosario
Calcite | Level 5

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

7 REPLIES 7
Tom
Super User Tom
Super User

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

wrosario
Calcite | Level 5

Tom,

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

Tom
Super User Tom
Super User

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

wrosario
Calcite | Level 5

ok Tom! veryTks....

Astounding
PROC Star

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
Calcite | Level 5

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
PROC Star

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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