topic Re: Look Up Table from Excel File in SAS Programming

Look Up Table from Excel File

LB3 — Thu, 25 Jun 2020 15:14:51 GMT

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

Re: Look Up Table from Excel File

AhmedAl_Attar — Thu, 25 Jun 2020 15:47:46 GMT

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

Re: Look Up Table from Excel File

Reeza — Thu, 25 Jun 2020 16:02:52 GMT

@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

Re: Look Up Table from Excel File

RichardDeVen — Fri, 26 Jun 2020 03:22:25 GMT

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