Help with More Efficient Code

[kiwi]

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;

[Astounding · Accepted solution]

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.

[kiwi]

Thank you! This helped!

[LinusH]

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.

[kiwi]

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!

[RW9]

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.

[RW9]

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.