BookmarkSubscribeRSS Feed
jpm2478
Calcite | Level 5

Hi,

 

I have a big dataset (A) of patients containing millions of rows and 50 columns. I need to do Multiple Hash lookups of the table using Macros. For example, I have patient info below by Patient Id. 

 

Pt_IdLocHQInvFill_LocFill_Qty
1058STLORD500MEM100
1099PITMEM50STL110
1058MCIMEM50ORD120
1058ORDMEM50ORI110
1059CDLIAD100MCI0
2051HOWHIT100IAD4
2045INDIAD5MCI5
2051MRDIAD1IND50
2051ORLIAD2LTY80
2051STKIAD5ORF40


1. I need to do Hash lookup on Pt_Id and Fill_Loc. For example, Pt_Id, Fill_Loc  

 

Pt_IdFill_LocFill_Qty
1058MEM100

 

2. Using the result from 1, I need to do look-up on Pt_id and Fill_loc from 1 will become HQ.....  

 

Pt_IdHQ
1058MEM

 

3.  From 1, Patient No. 1058 had a Fill_Qty of 100. From 2, I need to lookup Pt_Id, HQ and get Inv. If Fill_Qty was 100 then I need to show Inv of 100. 

 

Pt_IdLocInv
1058MCI50
1058ORD50

 

4. The total Inv should not be more them Fill_Qty. If Inv. was more then Fill_Qty then I need to check each record and reduce the last record so that the total will be equal to Fill_Qty.

 

Below is another example where Inv was more than Fill_Qty and I'll adjust the last inv. record to be equal to Fill_Qty.

 

1. Lookup 

Pt_IdFill_LocFill_Qty
2051IAD4

 

2. 

Pt_IdHQInv
2051IAD1
2051IAD2
2051IAD5

 

3. Result - Total of Inv. = Fill_Qty

Pt_IdHQInv
2051IAD1
2051IAD2
2051IAD1

The last row was adjusted from 5 to 1 so that the total of Inv matches Fill_Qty.

6 REPLIES 6
jpm2478
Calcite | Level 5

Here is the full dataset for 1058.

 

Pt_IdLocHQInvFill_LocFill_Qty
1058STLORD500MEM100
1058MCIMEM50ORD120
1058ORDMEM50ORI110
1058FITMEM10MEM10
jpm2478
Calcite | Level 5
Any suggestions?
PeterClemmensen
Tourmaline | Level 20

So, just to get things started, here is the first part (1).

 

data have;
input Pt_Id $ Loc $ HQ $ Inv Fill_Loc $ Fill_Qty;
datalines;
1058 STL ORD 500 MEM 100
1099 PIT MEM 50 STL 110
1058 MCI MEM 50 ORD 120
1058 ORD MEM 50 ORI 110
1059 CDL IAD 100 MCI 0
2051 HOW HIT 100 IAD 4
2045 IND IAD 5 MCI 5
2051 MRD IAD 1 IND 50
2051 ORL IAD 2 LTY 80
2051 STK IAD 5 ORF 40
;

data want;
   length Fill_Qty 8;
   if _N_ = 1 then do;
      declare hash h(dataset:"have");
      h.defineKey('Pt_Id','Fill_Loc');
      h.defineData('Fill_Qty');
      h.defineDone();
      call missing(Fill_Qty);
   end;

   Pt_Id="1058";Fill_Loc="MEM";

   h.find();
run;

Next: From question 2: "Fill_loc from 1 will become HQ".. I don't understand that? Please elaborate

jpm2478
Calcite | Level 5

You cannot hard code Pt_id and Fill_Loc. It is basically a loop. In the first run it is looking for 1058 and MEM Second run it will look for 1099 and STl and so on.

 

Coming to your question about Fill_Location will be HQ ....So, the Ist lookup is Pt_id and Fill_Loc. Now, the output has to be looked up under Pt_id and HQ.....MEM has to be filtered under Variable HQ.

 

Pt_IdFill_Loc
1058MEM

 

Pt_IdHQ
1058MEM
Patrick
Opal | Level 21

@jpm2478

If you're after fully working code then start by providing representative sample data for everything - and provide this sample data in form of tested SAS data steps so we've got something to start with.

Then explain in detail the problem you have to solve - one step at a time.

May be break up the problem into steps, give it a first go and come back here asking questions for the things you get stuck with - and also post the code you've already developed.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 927 views
  • 1 like
  • 4 in conversation