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.
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;
Sounds like your join criteria is that SK <= QTDE.
Tom,
this does not work, it will always go back and get that record do not match that name.
I do not use Data Integration Studio so I cannot help with how to get it do what you want.
ok Tom! veryTks....
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.
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.