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

SAS Folks-

I have hit a sticky problem in trying to consolidate code that is running in several places to just one set of code.

The scenario is that I'm picking groups of data out of our Oracle database, adding some other data, and then performing some specific operations, all of which depends on the initial group selection.  The groups are defined by macro variable PROJ_RUN, the dataset from Oracle is dataset &DATA_LIB..&DSN_FINALS, and the additional data is SPECIALS. 

I have sucessfully set up SQL from Oracle to get &DATA_LIB..&DSN_FINALS, and set up the SPECIAL data to add.  I'm having trouble with the last part, where I need to do some operations on the 'whole' dataset. (these are simple line deletions or calculations based on the value of a variable).

In the following data step, I would like to run only the bits inside the DO loop specified by the value of &PROJ_RUN, but everything in the data step runs whether the value of &PROJ_RUN is IRL or NCB.  When I get through, there will probably be 15 or so groups, so I'm looking for a low maintenance solution.

Thanks for any advice you can give me.

Wendy T

DATA &DATA_LIB..&DSN_FINALS ; SET &DATA_LIB..&DSN_FINALS SPECIALS ;

   IF COMPARE("&PROJ_RUN","IRL")=0 THEN
     DO ;
       %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;
       %INCLUDE "&UTILITY\COLUMN_IF_DELETE_FIELD_J_CODES.SAS"  ;
       %INCLUDE "&UTILITY\ADD_IRL_SEGMENTS.SAS";
     END ;

   IF COMPARE("&PROJ_RUN","NCB")=0 THEN
     DO ;
       %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;
       %INCLUDE "&UTILITY\CALC_NCB_CODES.SAS"  ;

     END ;

RUN ;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

COMPARE is not really needed in this situation. You do not seem to care at what position in the string the values diverge.

Just use the = operator.  "&proj_run"='IRL'

Also watch for case of your macro variable value.  Are you sure that it is uppercase?  You could try

IF "%upcase(&proj_run)"='IRl'

Also what do you mean by running all of it.  You do realize that the %INC's will happen unconditionally.  If you want to make it conditional you need to wrap this code inside a macro so that you could convert your IF statement to %IF statement.

View solution in original post

7 REPLIES 7
PGStats
Opal | Level 21

Just a guess. You might have leading or trailling blanks in your macro variable, try COMPARE(TRIM("&PROJ_RUN"),"IRL","L").

PG

PG
Tom
Super User Tom
Super User

COMPARE is not really needed in this situation. You do not seem to care at what position in the string the values diverge.

Just use the = operator.  "&proj_run"='IRL'

Also watch for case of your macro variable value.  Are you sure that it is uppercase?  You could try

IF "%upcase(&proj_run)"='IRl'

Also what do you mean by running all of it.  You do realize that the %INC's will happen unconditionally.  If you want to make it conditional you need to wrap this code inside a macro so that you could convert your IF statement to %IF statement.

WendyT
Pyrite | Level 9

Tom-

Macro was the key!  I absolutely did not know that the %INCLUDE bits needed to be wrapped - and that was my main issue.

The following runs beautifully.

Thanks so much for your help!

Wendy T

%MACRO ADDSPECIAL ;

DATA &DATA_LIB..&DSN_FINALS ;

  SET &DATA_LIB..&DSN_FINALS SPECIALS ;

   %IF "&PROJ_RUN"="IRL"  %THEN

           %DO ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_FIELD_J_CODES.SAS"  ;

     %INCLUDE "&UTILITY\ADD_IRL_SEGMENTS.SAS";

       %END ;

   %IF "&PROJ_RUN"="NCB"  %THEN

          %DO ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

     %INCLUDE "&UTILITY\CALC_NCB_CODES.SAS"  ;

         %END ;

%MEND ;

%ADDSPECIAL ; RUN ;

Tom
Super User Tom
Super User

You probably still need to take more care about the order of macro code expansion and code generation.

You have the start of the data step inside the macro but not the end.  Why?  Did you intend for the user of the macro add more data step code after the macro call?  If so then perhaps you should just remove the DATA and SET statments also?

%MACRO ADDSPECIAL(type) ;

%IF "&type"="IRL" %THEN %DO ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_FIELD_J_CODES.SAS"  ;

     %INCLUDE "&UTILITY\ADD_IRL_SEGMENTS.SAS";

%END ;

%IF "&type"="NCB" %THEN %DO ;

     %INCLUDE "&UTILITY\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

     %INCLUDE "&UTILITY\CALC_NCB_CODES.SAS"  ;

%END ;

%MEND addspecial ;

DATA &DATA_LIB..&DSN_FINALS ;

  SET &DATA_LIB..&DSN_FINALS SPECIALS ;

  %ADDSPECIAL(&proj_run) ;

run; 

WendyT
Pyrite | Level 9

Tom-

That was actually a sloppy cut-and-paste on my part - there is more code, but I omitted it for simplicity, and forgot to put the RUN; in at the bottom.

Your idea of pulling out just the conditions in the macro just gave me another idea - I could very easily pull the code for %ADDSPECIAL out into a separate file, rather than having to maintain it in place.

Thanks for all the great ideas!

Wendy T   

PGStats
Opal | Level 21

If you are looking to lowering your long term maintenance effort, you might be better served with the structure :

DATA &DATA_LIB..&DSN_FINALS .;

SET &DATA_LIB..&DSN_FINALS. SPECIALS ;

   SELECT (UPCASE(TRIM(LEFT("&PROJ_RUN."))));

     WHEN("IRL") DO ;

       %INCLUDE "&UTILITY.\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

       %INCLUDE "&UTILITY.\COLUMN_IF_DELETE_FIELD_J_CODES.SAS"  ;

       %INCLUDE "&UTILITY.\ADD_IRL_SEGMENTS.SAS";

     END ;

 

     WHEN("NCB") DO ;

       %INCLUDE "&UTILITY.\COLUMN_IF_DELETE_SECCHI_L_CODES.SAS" ;

       %INCLUDE "&UTILITY.\CALC_NCB_CODES.SAS"  ;

     END ;

 

     OTHERWISE;

 

END;

RUN ;

PG

PG
WendyT
Pyrite | Level 9

PG-

Great tip on looking for leading and trailing blanks - as it turns out, there was not an issue in this case.

I really like the SELECT WHEN - OTHERWISE structure you suggested.

I will play around with functions and your suggested structure to make this better now that I have a working solution.

Thanks so much for your help!

Wendy T

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!

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
  • 7 replies
  • 1062 views
  • 6 likes
  • 3 in conversation