topic Re: Adding a Calculated Field When Joining Two Tables via a Hash Table in SAS Enterprise Guide

Adding a Calculated Field When Joining Two Tables via a Hash Table

paulrockliffe — Thu, 21 May 2020 16:16:12 GMT

I have the programme below that I use in Enterprise Guide to subset a massive table. I want to add some calculated fields at the same time, to allow me to subset the table further for various conditions.

How do I do that?

data WORK.EXAMPLE (keep = Var1 Var2 Var3 Var4 Var5) ;

if 0 then set WORK.HASH WORK.SOURCE;

if _n_ = 1 then do;

declare hash l (dataset:"WORK.HASH");
l.definekey("Var2");
l.definedata("Var2r", "Var1", "Var3");
l.definedone();

end;

set WORK.SOURCE ;

if l.find() = 0

Output;

Run;

I can do it quite easily with Query Builder, but I'm trying to avoid creating a second copy of the data on my server - The initial subset creates a 14Gb file, so it's not one to duplicate!

Thanks.

Re: Adding a Calculated Field When Joining Two Tables via a Hash Table

Kurt_Bremser — Thu, 21 May 2020 16:21:40 GMT

Use the find method in a subsetting if:

if l.find() = 0;

and then add your calculations after this; if you need any further subsetting, use further subsetting IFs.

Re: Adding a Calculated Field When Joining Two Tables via a Hash Table

paulrockliffe — Thu, 21 May 2020 16:58:44 GMT

Thanks, you couldn't just add a quick example for me, I'm not sure I really have the syntax for this straight in my head!

Cheers

Re: Adding a Calculated Field When Joining Two Tables via a Hash Table

Kurt_Bremser — Thu, 21 May 2020 17:31:34 GMT

set WORK.SOURCE ;
if l.find() = 0;
/* add aditional code here */
run;

Since I don't know what exactly you want to do, I can't write code for it.

Re: Adding a Calculated Field When Joining Two Tables via a Hash Table

paulrockliffe — Thu, 21 May 2020 18:11:57 GMT

Thanks, I'm creating a key by concatenating two of my variables and a flag that is 1 if one of the variables is over 100.

I want to add those as new variables rather than subsetting directly as I'm running a few different operations from the resulting dataset.

Re: Adding a Calculated Field When Joining Two Tables via a Hash Table

Kurt_Bremser — Thu, 21 May 2020 19:27:25 GMT

So you do just a few simple assignments, and one of those conditionally (if-then). Nothing fancy, just insert the code where I put the comment.