BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kiwi
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

6 REPLIES 6
Astounding
PROC Star

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
Fluorite | Level 6
Thank you! This helped!


LinusH
Tourmaline | Level 20
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.
Data never sleeps
kiwi
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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