Hello, I am on SAS 9.2 I have two tables: The FIRST Table tells if the info is required: DATA WORK.FIRST;
INPUT CODE INFO $ REQUIRED $;
DATALINES;
1 ID YES
2 Group NO
3 Class NO
4 FirstName YES
5 City YES
6 Gender NO
7 Age YES
;
RUN; I have a SECOND table that contains informations: DATA WORK.SECOND;
INPUT ID FIRSTNAME $ CITY $ AGE TEAM $ ADDRESS $;
DATALINES;
1 TOM PARIS 25 TEAM_A ADDRESSA
22 JOHN TOKYO 20 TEAM_B ADDRESSB
13 PATRICIA ROMA 18 TEAM_B ADDRESSB
4 JOHANA LONDON 17 TEAM_A ADDRESSA
15 PHILLIP NEWYORK 32 TEAM_A ADDRESSA
6 LOUIS ATLANTA 26 TEAM_A ADDRESSA
47 SALLY MADRID 30 TEAM_B ADDRESSB
;RUN; I want to scan each and every row of the FIRST table and lookup in the variables of the SECOND table to check if the info is present if yes then write in a THIRD table: The program should do something like this: Scan the 1st row of the FIRST table, check if info is present in the 1st row of the SECOND table if yes: write in the THIRD TABLE. Scan the 2nd row of the FIRST table, check if info is present in the first row of the SECOND table if yes: write in the THIRD TABLE. ... Scan the last row of the FIRST table, check if info is present in the 1st row of the SECOND table if yes: write in the THIRD TABLE. Scan the first row of the FIRST table, check if info is present in the 2nd row of the SECOND table if yes: write in the THIRD TABLE. ... Scan the last row FIRST table, check if info is present in the last row of the SECOND table if yes: write in the THIRD TABLE. The THIRD table should look like this: DATA WORK.THIRD;
INPUT ELEMENT $ INFO $;
DATALINES;
ID 1
Group .
Class .
FirstName TOM
City PARIS
Gender .
Age 25
ID 22
Group .
Class .
FirstName JOHN
City TOKYO
Gender .
Age 20
ID 13
Group .
Class .
FirstName PATRICIA
City ROMA
Gender .
Age 18
ID 4
Group .
Class .
FirstName JOHANA
City LONDON
Gender .
Age 17
ID 15
Group .
Class .
FirstName PHILLIP
City NEWYORK
Gender .
Age 32
ID 6
Group .
Class .
FirstName LOUIS
City ATLANTA
Gender .
Age 26
ID 47
Group .
Class .
FirstName SALLY
City MADRID
Gender .
Age 30
;RUN; In conclusion my question is: How to perform a lookup between the observations of a table and the variables of an other table using a loop pattern ? At the beginning I thought about nested data step, but I figured out that it is not possible. I also thought about saving all the entries of the SECOND table in macro variables (transforming the SECOND Table in a matrix of macro-variables that I can call using Index and Position of each cell) and then do a data step using the FIRST table and the macrovariables but I am pretty sure it is not efficient. I hope this is pretty clear, indeed my real tables are much bigger, this is a simplified example to ease the understanding. Do you have any idea about that ? Thank you.
... View more
