topic Re: how to best work with 2 related tables with different formats, for conditional scoring in SAS Programming

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

Cal_Hottie — Wed, 13 Nov 2024 19:19:25 GMT

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!

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

Tom — Wed, 13 Nov 2024 19:50:45 GMT

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.

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

Cal_Hottie — Wed, 13 Nov 2024 20:58:48 GMT

This worked, thanks!