Automate case expression in SAS

[Flamdrags5]

Here is the code I am trying to automate:

Proc sql;
create table Common_Policy as
select *,

case when POL_YYYY = 2005 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP05a,
case when POL_YYYY = 2006 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP06a,
case when POL_YYYY = 2007 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP07a,
case when POL_YYYY = 2008 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP08a,
case when POL_YYYY = 2009 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP09a,
case when POL_YYYY = 2010 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP10a,
case when POL_YYYY = 2011 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP11a,
case when POL_YYYY = 2012 Then SUM_of_DIR_WP_AMNT Else 0 End as DWP12a,

case when POL_YYYY = 2005 Then SUM_of_WRIT_EXPOS_AMNT Else 0 End as EXP05a,
case when POL_YYYY = 2006 Then SUM_of_WRIT_EXPOS_AMNT Else 0 End as EXP06a,    
case when POL_YYYY = 2007 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP07a,    
case when POL_YYYY = 2008 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP08a,    
case when POL_YYYY = 2009 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP09a,    
case when POL_YYYY = 2010 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP10a,    
case when POL_YYYY = 2011 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP11a,
case when POL_YYYY = 2012 Then SUM_OF_WRIT_EXPOS_AMNT Else 0 End as EXP12a

from Drop_Zeros;
quit;

Here is what I have written:

%macro CP(start_py=,end_py=,curr=);
data Common_Policy;
set Drop_Zeros;
%let start = %eval(&start_py - 1);
%let newC = %eval(&curr - 1);

%do %while (&start <= &end_py);
         if POL_YYYY = &start then %let DWP&del0&newC&dela = SUM_of_DIR_WP_AMNT;
         else %let DWP&del0&newC&dela = 0;

        if POL_YYYY = &start then %let EXP&del0&newC&dela = SUM_OF_WRIT_EXPOS_AMNT;
         else %let EXP&del0&newC&dela = 0;
         start = %eval(&start + 1);
         newC = %eval(&newC + 1);
%end;
run;
%mend CP;

%CP (start_py=&start_policy_year, end_py=&end_policy_year, curr=&current);

Variables &end_policy_year, &start_policy_year , and &current are global variables which would be input by the user at the very top of the code. Here, they would be 2006, 2012, and 06, respectively.

I keep running into errors with this code saying that the macro cannot be redefined.

Any ideas on how I can complete this macro as is or a whole new way of doing it?

[Reeza]

Start using some put statements to see what your macro is doing.

Somethings just looking at your code, &del0 &dela don't seem to be defined at all as macro variables.

Also check how newc is resolving.

You could consider using an array and datastep instead.

How fixed are you with your variable names of DWP01a and EXP01a?

Its easier if the a isn't there but still workable.

[Flamdrags5]

My bad. I have &del0 = 0 and &dela = a as global variables. I used them (among other similar variables) to create new variable names.

How would I go about doing it with a data step and array?

[mkeintz]

I think you're putting your macro at too high a level.  Use it to just generate var names in an array statement.

Presumably the final code should look something like this (for the dwp vars only):

data want;
   set have;
   array dwp {2005:2012} dwp05a dwp06a dwp07a dwp08a dwp09a dwp10a dwp11a dwp12a;
   dwp{pol_yyyy}=sum_of_dir_wp_amt;
run;

So the array declaration with lower bound 2005 and upperbound 2012 takes care of the "case when ..." logic.  Now the only problem is to generate an array with bounds determined by global macrovars start_policy_year and end_policy_year, and to generate a list of variable names using the last two digits of the year in the middle of the var names, and a fixed prefix (dwp) and fixed suffix (a).  So here's a macro:

%macro expand(pre=,suf=,beg=,end=);
  %do I=&beg %to &end; &pre%substr(&I,3,2)&suf %end;
%mend;

and here's how I would use it:

data want;
   set have;
   array dwp {&start_policy_year:&end_policy_year}  %expand(pre=dwp,suf=a,beg=&start_policy_year,end=&end_policy_year);

   array exp {&start_policy_year:&end_policy_year}  %expand(pre=exp,suf=a,beg=&start_policy_year,end=&end_policy_year);

   dwp{pol_yyyy}=sum_of_dir_wp_amt;

   exp{pol_yyyy}=sum_of_writ_expo_amt;
run;

You can easily test for out-of-bounds value of pol_yyyy, as in:

  if  &start_policy_year <= pol_yyyy <= &end_policy_year then do;
     dwp{ppl_yyyy} = sum_of_dir_wp_amt;
     exp{pol_yyyy}=sum_of_writ_expo_amt;
  end;

regards,
Mark

[Astounding · Accepted solution]

Here's an example of using a DATA step and arrays.  I changed the results slightly, to shorten the code.

There is no longer any use of &CURRENT.  That should be calculated anyway, if it is always the last two digits of the starting year.  The user should not have to enter redundant information.

The variable names are somewhat different, to take advantage of SAS naming conventions.

data want;

   set have;

   array DWP {&start_policy_year : &end_policy_year} DWPa_&start_policy_year - DWPa_&end_policy_year;

   array EXP  {&start_policy_year : &end_policy_year} EXPa_&start_policy_year - EXPa_&end_policy_year;

   do _n_=&start_policy_year to &end_policy_year;

       DWP{_n_}=0;

       EXP{_n_}=0;

   end;

   if (&start_policy_year <= POL_YYYY <= &end_policy_year) then do;

      DWP{POL_YYYY} = SUM_of_DIR_WP_AMNT;

      EXP{POL_YYYY} = SUM_of_WRIT_EXPOS_AMNT;

   end;

run;

If you really need the old variable names that can be arranged.  But start here and take it a step at a time.

Good luck.

[Flamdrags5]

Thanks so much!

Now how would I automate this next part:

proc sql;
create table one_line as
select Policy_13,
Pol_MM,
EXPOSURE_DESC,
SUM(DWPa_2005) as DWP2005,
SUM(DWPa_2006) as DWP2006,
...
SUM(DWPa_2012) as DWP2012,

SUM(EXPa_2005) as EXP2005,
SUM(EXPa_2006) as EXP2006,
...
SUM(EXPa_2012) as EXP2012

from Common_Policy
group by Policy_13, Pol_MM, EXPOSURE_DESC;
quit;

[Tom]

Why not just turn your data into a more vertical structure so that YEAR is recorded as the value of variable instead of as part of the name of the variable?

Then you can just add YEAR to your BY or CLASS variable list.

You can always rotate it back up if you need the wide structure for a report.

[Astounding]

For the second question, I would switch away from SQL.  SQL can be used, but automation would require more macro language because each sum has to be named.  Instead, PROC SUMMARY will use less macro language, taking advantage of variable lists:

proc summary data=common_policy nway missing;

   class policy_13 Pol_MM EXPOSURE_DESC;
   var DWPa_&start_policy_year - DWPa_&end_policy_year

        EXPa_&start_policy_year - EXPa_&end_policy_year;

   output out=one_line (drop=_type_ _freq_)

             sum=DWP&start_policy_year - DWP&end_policy_year

                     EXP&start_policy_year - EXP&end_policy_year;

run;

Tom's suggestion is worth considering mightily.  Including YEAR as a separate variable, and normalizing the data, gives you a lot of flexibility.

Good luck.

[tc]

Perhaps you can cobble something out of this....

It's a Bird, It's a Plane, It's SQL Transpose!
http://www2.sas.com/proceedings/forum2008/089-2008.pdf
Ever wish you could easily transpose and summarize monthly data using just PROC SQL? Regardless of whether the data is stored in a SAS dataset or other relational databases? Even if scores of variables for dozen of months are involved? This paper tackles the task of data denormalization with a simple macro that uses PROC SQL to take a "vertical" table with multiple rows per subject and transform it into a "horizontal" table containing one row per subject with aggregated monthly values.