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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

8 REPLIES 8
Reeza
Super User

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
Calcite | Level 5

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
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Astounding
PROC Star

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
Calcite | Level 5

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
Super User Tom
Super User

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
PROC Star

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
Lapis Lazuli | Level 10 tc
Lapis Lazuli | Level 10

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1549 views
  • 5 likes
  • 6 in conversation