topic Re: Performing a lookup between 2 tables using a loop pattern in SAS Programming

Performing a lookup between 2 tables using a loop pattern

Hugo_B — Tue, 30 Apr 2019 09:23:18 GMT

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.

Re: Performing a lookup between 2 tables using a loop pattern

FreelanceReinh — Tue, 30 Apr 2019 22:12:54 GMT

Hello @Hugo_B and welcome to the SAS Support Communities!

First of all, thank you very much for providing test data in the form of a DATA step. (To avoid truncation of the string "FirstName" I used a :$9. informat specification for variables INFO in FIRST and ELEMENT in THIRD, but this is only a minor issue.)

One way to create a dataset like THIRD is to apply PROC TRANSPOSE to a dataset (or view) which contains the information in THIRD in a wide format. The latter can be created from SECOND in a DATA step, including the metadata information from FIRST in the form of a variable list. This variable list in turn can be written by PROC SQL into a macro variable.

/* Create variable list from dataset FIRST */

proc sql noprint;
select info into :vlist separated by ' '
from first;
quit;

/* Create a temporary transposed version of the target dataset */

data wide;
retain &vlist;
if 0 then do;
  set second;
  call missing(of _all_);
end;
set second;
keep &vlist;
run;

/* Transpose from wide to long format */

proc transpose data=wide out=want(drop=id rename=(_name_=element col1=info));
by id notsorted;
var _all_;
run;

The resulting dataset WANT is almost identical to dataset THIRD. The differences are:

Variable ELEMENT in WANT has a default label from PROC TRANSPOSE (which could be deleted or modified with PROC DATASETS).
Variable INFO in WANT has length 12 (but this depends on the data) and its transposed (formerly) numeric values are right-justified in the $12 field, with periods rather than blanks for missing values.

I didn't use variable REQUIRED, but just all variable names listed in dataset FIRST.

Obviously, dataset WIDE is similar to dataset SECOND. So, another option might be to start with a transposed version of SECOND and then modify it so as to insert all the desired missing values for Group, Class and Gender.

Re: Performing a lookup between 2 tables using a loop pattern

ChrisNZ — Wed, 01 May 2019 04:05:32 GMT

Another way:

data _null_;
  set FIRST;
  link varexists;
  if _N_=1 then 
        call execute('data WANT; set SECOND; length ELEMENT INFO $10; INFO=cats(ID);');
  else if VAREXISTS and REQUIRED='YES' then 
        call execute('INFO=cats('||INFO||');');
  else  call execute('INFO=".       ";');
  call execute('ELEMENT="'||INFO||'"; output;');
  return;
  varexists:
  DSID=open('WORK.SECOND');
  VAREXISTS=varnum(DSID,INFO);
  DSID=close(DSID);
run;
run;

ELEMENT	INFO
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