CODEWITHSUNDEEP
mergesas
Plug4
Plug4

Reputation: 3928

SAS: Assign the missing values

I have a strange database that looks like this:

Client Order_number Original_number New_number
A      1            .               .
       .            1               2
B      3            .               .
C      4            .               .
       .            3               5
       .            5               6
       .            2               8
       .            4               9

Say for client A, he gets assigned an Order_number=1. Then for some reason, we had to change is Order_number for a New_number =2 My objective is to populate the above matrix for the missing blanks in the column Client as follow:

Client Order_number Original_number New_number
A      1            .               .
A      .            1               2
B      3            .               .
C      4            .               .
B      .            3               5
B      .            5               6
A      .            2               8
C      .            4               9

I can't think of an efficient way to do so without using MERGE.

Upvotes: 0

Views: 162

Answers (1)

Jeff
Jeff

Reputation: 1807

This program works, but it could probably be refactored a bit since I just threw it together. It assumes that the whole table is sorted like your sample. It uses hash tables, so if you're processing something big, this might run into memory limitations.

The idea is to build up a couple hash tables as you go so that you have an abstract of what appeared in the lines above.

data want (drop=rc map_:);
    if _n_ = 1 then do;
        declare hash orig_new();
        orig_new.definekey('map_original');
        orig_new.definedata('map_new','map_client');
        orig_new.definedone();

        declare hash new_client();
        new_client.definekey('map_new');
        new_client.definedata('map_client');
        new_client.definedone();
    end;

    set have;

    if order ne . then do;
        map_original = order;
        map_new = .;
        map_client = client;
        rc = orig_new.add();
    end;

    if client = '' then do;
        rc = orig_new.find(key:original);
        client = map_client;
    end;

    if new ne . then do;
        map_original = original;
        map_new = new;
        map_client = client;
        rc = orig_new.add();
        rc = new_client.add();
    end;

    rc = new_client.find(key:original);
    client = map_client;

run;

(I don't know if this is going to be helpful, but at least I enjoyed the exercise.)

Upvotes: 2

Related Questions