How to Efficiently Update Code

[CeciliaE]

Hi there!

I have a program that will need to be updated every year. It's basically a very long PROC SQL SELECT statement with a bunch of CASE WHEN statements that evaluate whether a variable in each of around 12 tables is within a range. Each range is based on a "base year" (in this case, '14). Here's a sample:

(CASE
WHEN t3.AY=14 AND t3.ASSOC_TR>0 THEN '4A'
WHEN t3.AY=14 AND t3.ASSOC_RT>0 THEN '4A'
WHEN t3.AY=14 AND t3.ASSOC_AAS>0 THEN '4A'

WHEN (t4.AY=15 OR t4.AY=14) AND t4.ASSOC_TR>0 THEN '4A'
WHEN (t4.AY=15 OR t4.AY=14) AND t4.ASSOC_RT>0 THEN '4A'
WHEN (t4.AY=15 OR t4.AY=14) AND t4.ASSOC_AAS>0 THEN '4A'

WHEN (t5.AY<=16 AND t5.AY>=14) AND t5.ASSOC_TR>0 THEN '4A'
WHEN (t5.AY<=16 AND t5.AY>=14) AND t5.ASSOC_RT>0 THEN '4A'
WHEN (t5.AY<=16 AND t5.AY>=14) AND t5.ASSOC_AAS>0 THEN '4A'

WHEN (t6.AY<=17 AND t6.AY>=14) AND t6.ASSOC_TR>0 THEN '4A'
WHEN (t6.AY<=17 AND t6.AY>=14) AND t6.ASSOC_RT>0 THEN '4A'
WHEN (t6.AY<=17 AND t6.AY>=14) AND t6.ASSOC_AAS>0 THEN '4A'
...

I have experience with Python, and if I were coding with Python, I would just create one variable for the reference year (base_year = 14) and then create other variables based on the base_year variable (bp1 = base_year + 1; bp2 = base_year +2; etc.). Then, I could just reference the variables throughout the code, and each year, I'd only have to update the base_year variable.

Is there a similar solution in SAS? Is there another efficient solution you can recommend? Thanks for any assistance you can provide!

[AlanC · Accepted solution]

This is simply a codegen problem. Lots of codegen solutions to choose from. I use C# to do it a lot but spent my final years at SAS doing codegen using SAS. Here is an example. Note: I have a utility on my website (postsas.com) that helps with codegen: 

   data _null_; *data TEST ;
       file CGSELECT ;
       set &reflib..&refTable. end=eof;
       if _n_ = 1 then
          do ;
             put   @3 '%include preProcessForCodeGen.sas;'
                 / @3 'SELECT; '
                 ;
          end;
       put   @6 'WHEN (';
       %include CGCONDTN ;
       put   @13 ') '
             @13 'DO;'
           / @13 '   CONDITION = ' _N_ ';'
            %do i = 1 %to &numValues.;
                %if &&assignVar&i.. NE %then 
                    %do ;
                        %PUT "ASSIGN=&&assignVar&i..";
                       / @13 "   &&outVar&i.. = &&assignVar&i.. ;" 
                    %end;
                %else %if &&outVartype&i.. = 1 %then 
                    %do;
                        %PUT 'IN 1';
                       / @13 "   &&outVar&i.. = &&outVartype&i.. ;"
                    %end;
                %else %if &&outVartype&i.. = 2 %then 
                    %do;
                        %PUT 'IN 2';
                       / @13 "   &&outVar&i.. = '&&outVartype&i..' ;"
                    %end;
            %end;

           / @13 'END;'
            ;
       if eof then
          do ;
             put @6   'OTHERWISE'
                 /@6  '   DO; '   
                 /@6  '       CONDITION=-99999; '
                  ; 
                 %* I think I prefer not defining in this case because we may want this process to create char data?;
             %include CGOTHER;
             put  @6  '   END; '   
                 /@3 'END;' 
                 /@3 '%include postProcessForCodeGen.sas;'
                 ;
          end;
    run; 

[AlanC]

BTW, after more than a decade of being a SAS macro dude, I got so irritated by them I switched to codegen for all macro type functionality. I did this when I was an consultant at SAS. Codegen provides easy to see and test code. For SAS, I highly recommend it: you are on the right path with the question. 

[CeciliaE]

This is great--thanks so much!

[ballardw]

Please pardon picking apart incomplete code but from what you have actually shown the "efficient code" would appear to be

'4A' as somevarname

When the code shown only has a single result then following the "logic" is very difficult to actually comprehend.

I also have a feeling that the joining of as a many data sets as you seem to imply, with additional set(s) every year, may indicate a data structure issue and the code changing to accommodate is possibly extra work because of a structure choice.

[Patrick]

If you'd show us the full SQL we could eventually propose a more efficient way. If it was me then I'd try to implement code that I never need to change - like also derive the base date from the calendar date.

[SASKiwi]

It you are having to change your code every year because there are new tables to read there is something seriously wrong with your underlying data model. Better to fix the cause of the disease rather than bandaid it. Providing the full SQL query might provide clues to a better solution.

[Tom]

That looks like SQL code.

Why not re-write the program using SAS code instead?