how to best work with 2 related tables with different formats, for conditional scoring

[Cal_Hottie]

I am working with 2 data sets  that need to coordinate. They are set up as follows:

Table 1. Date_Zip table with numerical value for each date zip combo:

Date _90001 _90002 ... _96162

01-01-2016 15.35 30.29 39.08

...

12-31-2020 30.55 62.75 55.14

Table 2. Risk and threshold by zip

Zip  Risk (numerical) Threshold (numerical)

90001 1.0 18.36

90001 1.5 20.16

90001 2.0 23.75

90001 2.5 25.11

...

96162 1.0 23.22

96162 1.5 25.45

96162 2.0 28.85

96162 2.5 30.44

Am trying to figure out the best way to work with these tables. The goal is to determine a score for each table 1 zip date value based on the table 2 zip specific risk and threshold values.

* the risk values will determine the nominal score (<1 risk = score 0, 1-1.5 risk = score 1, 1..5-2.0 risk = score 2, 2.0-2.5 risk = score 3, >2.5 risk = score 4). 

My final table would have a unique score for each day for each zip. 

Thanks!

[Tom · Accepted solution]

Convert your first dataset into something that more closely resembles your description.  A series of DATE/ZIP pairs.  

proc transpose data=Date_Zip out=tall;
  by date ;
  var _: ;
run;
data tall;
  set tall;
  zip=input(substr(_name_,2),5.);
run;

Now you can combine the data by ZIP value.  You might also want to rename the generated COL1 variable to something that is more meaningful to make the code clearer.

[Cal_Hottie]

This worked, thanks!