topic Re: Help with More Efficient Code in SAS Procedures

Help with More Efficient Code

kiwi — Mon, 16 May 2016 22:37:56 GMT

I'm trying to figure out a way to use a loop and/or macro to simplify this code where I'm setting up 99 columns (ExposureUnits01, ExposureUnits02, etc.)

PROC SQL;

CREATE TABLE EXAMPLE02 AS

SELECT

Policy,

UNITS01* ExposureCount01 as ExposureUnits01,

UNITS02* ExposureCount02 as ExposureUnits02,

UNITS03* ExposureCount03 as ExposureUnits03,

UNITS04* ExposureCount04 as ExposureUnits04,

/*(… ETC…)*/

UNITS99* ExposureCount99 as ExposureUnits99

FROM EXAMPLE01;

QUIT;

Re: Help with More Efficient Code

Astounding — Mon, 16 May 2016 23:43:45 GMT

This won't make the program run any faster, but it can simplify your life as a programmer. Consider a macro:

%macro all99;

%local i;

%do i=1 %to 99;

, UNITS%sysfunc(putn(&i, z2)) * ExposureCount%sysfunc(putn(&i, z2)) as ExposureUnits%sysfunc(putn(&i, z2))

%end;

%mend all99;

Then call the macro in the middle of the SELECT statement:

proc sql;

create table example02 as select policy, %all99 from example01;

quit;

The leading zeros complicates things a bit. It would be simpler if you wanted ExposureUnits1-ExposureUnits99 instead of ExposureUnits01-ExposureUnits99.

You'll have to try it to verify that it works ... I can't test it right now. But if there are any issues they will be straightforward to fix.

Good luck.

Re: Help with More Efficient Code

LinusH — Tue, 17 May 2016 02:37:20 GMT

My simple answer is, don't.
Examples like this show how awkward ig is to work with column heavy (aka long) table layout.
Transpose your table and there is only need for a single assignment.

Re: Help with More Efficient Code

RW9 — Tue, 17 May 2016 09:06:34 GMT

Firstly, let me say this quite clearly, Macro language does Not make code more efficient! If anything it merely obfuscates the code.

Secondly, I totally agree with @LinusH, it is never a good idea to create many variables, this number may change over time, so your structure will keep changing. It is far easier to program with observations where structure does not change each run. Also to note on this, SQL is a language designed to work with normalised data, so this type of processing is really not good to do with SQL. In SAS you have arrays, but even then I would suggest a new observation per data item rather than column.

So get yourself a dataset which has one observation per item:

POLICY EXPOSURE_EVENT EXPOSURE_COUNT EXPOSURE_UNITS

XYZ 1 12 abc

XYZ 2 87 abc

...

You will see the above structure would not change no matter how many events there are - makes your programming easier.

Re: Help with More Efficient Code

kiwi — Tue, 17 May 2016 22:26:55 GMT

Thank you! This helped!

Re: Help with More Efficient Code

kiwi — Tue, 17 May 2016 22:32:27 GMT

Thank you. I was trying to get away from making hundreds of columns, but I don't think I can avoid it. Each policy will have 120 years of mortality rates. I think I'd be worse off by transposing it? I'll think about it though. Still new to programming so I appreciate the help!

Re: Help with More Efficient Code

RW9 — Wed, 18 May 2016 08:04:54 GMT

Of course you can avoid it. Databasing, and programming have been around for 40 odd years using normalised data. Understand your data, and how you want to work with, then strcuture it in a way that makes your life easiest, this is the core process of a programmer.