topic Re: Look-up from table with top and row and left column as indices in SAS Programming

Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 12:41:21 GMT

I know how to do this fairly easy in Python pandas using lookup() but want to see how to do it in SAS. The problem is straight forward. I have this master list show the zone associated with any zip-code pair. For instance, if the origin zip code is 95826 and the destination zip code is 73864, then the corresponding zone is 6. This master list has 3000 rows and columns. I would appreciate keeping the code macro free. I just need the fundamental base SAS code or ideas and I can take it from there. Many people have suggested transposing then doing a join/merge. I don't think that will work here. Currently my office uses Excel VBA to do this then feeds the results back into SAS. Considering what we pay for SAS, I'm certain there's a way to do this in SAS. In python, the solution involves re-indexing the "Master List" data set so the zip codes along the vertical are the new row indices instead of the default 1,2,3,4,....

Thanks in advance.

For our discussion, let's say I got the data into SAS and it looks like the following

Master List of zones:

1 FILL 73864 37463 37465 72958 20276

2 95826 6 1 5 8 2

3 98574 2 9 1 9 1

4 23847 1 3 8 4 7

5 95867 3 2 5 8 1

6 39487 . . . . .

7 39483 . . . . .

Desired Output:

Package_Weight Package_Height Package_Length Entry_zip Destination_zip zone

2 10 32 95867 20276 1

6 3 12 23847 37465 8

Re: Look-up from table with top and row and left column as indices

Astounding — Sun, 26 Aug 2018 12:11:00 GMT

In SAS, you would transform the data set into from-to pairs. For example, the first few observations might look like this:

From To Value

95826 73864 6

95826 37463 1

95826 37465 5

That data set would be useful within a DATA step as a hash table, where the FROM TO combination is the index to the hash table, used to retrieve the VALUE.

If you need to retrieve the values in a procedure rather than a DATA step, there are other possibilities. But those possibilities still begin with transforming the data.

Re: Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 12:12:43 GMT

Astounding, how do I get it in the form you suggest? Transpose will not do that.

Re: Look-up from table with top and row and left column as indices

Ksharp — Sun, 26 Aug 2018 12:22:24 GMT

What kind of style did your data store? A sas table or a Excel ? 12345 is not a valid sas name.

Here are some ways:

1) best way is using SAS/IML code.

2) Hash Table

3)Proc format

4) Array Skill .

Re: Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 12:27:28 GMT

The master zip code list is an Excel doc. 12345 is not the sas file name; I'm saying the row observations are named sequentially as is normally the case 1 thru (# of observations).

Re: Look-up from table with top and row and left column as indices

Astounding — Sun, 26 Aug 2018 12:32:34 GMT

Can you get the data into a SAS data set in any form at all? Clearly what you posted is prior to that step. So get the data into SAS, and describe the structure of what it looks like.

Re: Look-up from table with top and row and left column as indices

Ksharp — Sun, 26 Aug 2018 12:36:03 GMT

Then you need offer this excel file.

You need import this excel file into sas before doing look-up operator .

Re: Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 12:42:18 GMT

I've updated the question to what the data set would look like in SAS.

Re: Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 12:43:00 GMT

I've posted what the data will look like in SAS

Re: Look-up from table with top and row and left column as indices

Ksharp — Sun, 26 Aug 2018 12:56:01 GMT

OK. Here is an example.

The best way is using IML, if you have SAS/IML .

option validvarname=any;
data have;
input FILL      '73864'n    '37463'n    '37465'n    '72958'n     '20276'n;
cards;
   95826              6            1            5            8             2
    98574              2            9            1            9             1
    23847              1            3            8            4             7            
    95867              3            2            5            8             1
;
run;

proc sort data=have out=temp;
by fill;
run;
proc transpose data=temp out=lookup(where=(_NAME_ ne 'FILL'));
by fill;
var _all_;
run;
data lookup;
 set lookup;
 name=input(_name_,best32.);
run;
data want;
 if _n_=1 then do;
  if 0 then set lookup;
  declare hash h(dataset:'lookup');
  h.definekey('fill','name');
  h.definedata('col1');
  h.definedone();
 end;

Entry_zip=95867;    Destination_zip=20276; 
if h.find(key:Entry_zip,key:Destination_zip)=0 then zone=col1;
output;


Entry_zip=23847  ;    Destination_zip=37465  ; 
if h.find(key:Entry_zip,key:Destination_zip)=0 then zone=col1;
output;


run;

Re: Look-up from table with top and row and left column as indices

Badjuju — Sun, 26 Aug 2018 13:08:34 GMT

Thanks for this Ksharp. I need to read-up on this hash stuff and digest this code.

Re: Look-up from table with top and row and left column as indices

Tom — Sun, 26 Aug 2018 14:35:11 GMT

Note that postal codes are NOT numbers. For example the code 04032 for Freeport Maine has a leading zero.

How to start depends on what format your original list is in. If it is a CSV or other type of text file then just read it in originally as from/to pairs. For example if the file looks like this:

FILL 73864 37463 37465 72958 20276
95826 6 1 5 8 2
98574 2 9 1 9 1
23847 1 3 8 4 7
95867 3 2 5 8 1

you could read the first line and remember the codes for the columns.

data zones ;
  infile zone truncover ;
  array cols (3000) $7 _temporary_;
  length from to $7 zone 8;
  if _n_=1 then do;
    input to @;
    do col=1 by 1 until(to=' ') ;
      input to @ ;
      cols(col)=to;
    end;
    ncols=col-1;
    retain ncols;
    input ;
  end;
  input from @;
  do col=1 to ncols ;
    to=cols(col);
    input zone @;
    output;
  end;
  keep from to zone;
run;

Now you can either use the data in a hash and join or merge or create a format or informat.

You could easily transform the FROM/TO/ZONE variables into START/LABEL values to use with PROC FORMAT's CNTLIN= option.

data infmt;
  set zones ;
  retain fmtname 'ZONE' type 'I' ;
  length start $14 ;
  start = cat(from,to);
  rename zone=label;
run;
proc format cntlin=infmt;
run;

Now if we create your example data.

data have;
  input Package_Weight Package_Height Package_Length Entry_zip :$7.  Destination_zip :$7. zone_expect ;
cards;
 2 10 32 95867 20276 1
 6 3 12 23847 37465 8
;

It is easy enough to use the INFORMAT to convert the from/to pair into a zone.

data want ;
 set have ;
 zone = input(cat(entry_zip,destination_zip),zone.);
run;

       Package_    Package_    Package_    Entry_    Destination_     zone_
Obs     Weight      Height      Length      zip          zip         expect    zone

 1         2          10          32       95867        20276           1        1
 2         6           3          12       23847        37465           8        8