turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Automate case expression in SAS

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2012 04:37 PM

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=¤t);

Variables &end_policy_year, &start_policy_year , and ¤t 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?

Accepted Solutions

Solution

12-04-2012
05:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-04-2012 05:10 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-04-2012 03:36 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

12-04-2012 04:48 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-04-2012 05:03 PM

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

Solution

12-04-2012
05:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-04-2012 05:10 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

12-05-2012 06:55 PM

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**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-05-2012 08:18 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-06-2012 09:23 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flamdrags5

12-06-2012 10:35 AM

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.