Look Up Table from Excel File

[LB3]

I need some help using a lookup table in SAS that looks like the following. The table comes from an Excel file that produced by someone else and I have no way to change the structure of the table. I would like to be able use the table to join where the Term and Date in the first row match another table. Any way to accomplish this in SAS?

Term	6/1/2020	6/2/2020	6/3/2020	6/4/2020
1	10	11	12	13
2	9	10	11	12
3	8	9	10	11
4	7	8	9	10

[AhmedAl_Attar]

Try this

LIBNAME exfiles XLSX '[drive:]\test_xlsx_lkup.xlsx';
options validvarname='any';

proc contents data=exfiles._all_; run;
proc sql;
	select term
		,'43986'n
	from exfiles.sheet1;
quit;

Additional examples can be found here

Using LIBNAME XLSX to read and write Excel files 

Accessing Excel files using LIBNAME XLSX

Hope this helps,

Ahmed

[Reeza · Accepted solution]

---

@LB3 wrote:

The table comes from an Excel file that produced by someone else and I have no way to change the structure of the table.

---

Why do you have no way to change the structure? Import the data from Excel and then use PROC TRANSPOSE and a standard merge after that. Assuming your date variables are named something like _date1 _date2 this may work. There are ways to easily list all your other variables if you don't have a naming convention.

proc transpose data=dates out=long_dates;
by term;
var _: ; *you need to put the list of variable names here;
run;

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

[RichardDeVen]

How are you reading the Excel table ?

If you Proc IMPORT the  Excel file the date valued columns in SAS will be named _<excel-date-number>.

Perform a transpose BY TERM and compute the SAS date value from the _NAME_ value.

Example (untested)

Proc IMPORT datafile='SomeoneElse.xlsx' dbms=xlsx replace out=lookup_raw;
Proc TRANSPOSE data=lookup_raw out=lookup_base;
  by TERM;
  var _:;
run;
data lookup;
  set lookup_base;
  xl_date = input (substr(_name_,2),8.);
  date = xl_date + '31dec1899'd;
  format date date9.;
run;

Now you can join

select
   ...
   , COL1 as LookupValue
from 
  other_table as A
join 
  lookup as LU
on 
  A.date = LU.date and A.TERM=LU.TERM