SAS Array Help

[rahulp]

		proc sql noprint;
			select max(EXCEPTION_CD)
			into :no_exc
			from REWARDS.REGISTRATION
			where not missing(EXCEPTION_CD)
			;
		quit;

		%let no_exc = &no_exc;

		%put no_exc: &no_exc;

		/* Get the unique exception codes and stored them in macro variables excx */
		proc sql noprint;
			select distinct EXCEPTION_CD 
			into :exc1-:exc&no_exc
			from REWARDS.REGISTRATION
			where not missing(EXCEPTION_CD)
			;
		quit;

		/* Loop through the number of exceptions and stored the candidate codes, candidate group codes and exception types associated to each exception code
		   in macro variables */
		%do i = 1 %to &no_exc;

			%global cnd_cd_exc&i cnd_grp_cd_exc&i exc_type&i;

			proc sql noprint;

				select distinct quote(CND_CD), quote(CND_GRP_CD), EXCEPTION_TYPE
				into :cnd_cd_exc&i separated by ',', :cnd_grp_cd_exc&i separated by ',', :exc_type&i
				from REWARDS.REGISTRATION
				where EXCEPTION_CD = "&&exc&i";
			quit;

			%put codes: &&cnd_cd_exc&i;
			%put grp_cd: &&cnd_grp_cd_exc&i;
			%put types: &&exc_type&i;

		%end;

Hello,

I tried searching but am unable to get any results relevant to my issue.

What I am trying to Achieve:

I have a csv file with Exception Codes ( 1-99), however, only 10 are being used currently (1-10). Each has an exception logic. For example:

Code 1 is for Sales, as in, they met Sales goal, if though they did not, so Sales Exception = Sales * Reward Dollar.

Code 2 is for Age, as in, they qualify for Senior Tier, Age Exception = Sales * Reward Dollar * Age Multiplier

Code 3 is for Disability, as in they qualify for Disable Tier = Sales * Reward Dollar * Disability Multiplier

Code 4 is for Region, as in they qualify for Region Tier = Sales * Reward Dollar * Region Multiplier

I have the below code that generates the Array in a Macro, and it works great. However, that only stands true as long as all the 4 (1-4) Codes Exist or all 10 exist. Under the code, lets say, if Code 2 does not exist, it would put Code 3 under Code 2, and thus the calculation would be wrong. Same would happen with Code 4, which will now be Code 3.

All the below is done in a Macro Code, followed by utilization using "%if %symexist(cnd_cd_exc1) %THEN %DO;", and this statement follows the calculation logic. (This is the statement after the symexist: if CANDIDATE_CD in (&&cnd_cd_exc1) then do;)

What I want from below is, Code 1 is always exc1 and Code 3 is always exc3, weather code 2 exist or does not exist.

Do let me know if you need anything else. 

Please Note: The whole question is arbitrary, as specifics are obviously confidential. The example is just an example, and provides a way for my solution.

Specifics: SAS 9.3 on Unix. All runs are ni EG 5.1

[Oligolas]

Hi,

If you know you may miss some codes, why don't you check them and use placeholders to replace the missing ones?

I'd rather recommend using SAS datasets and proc transpose instead of macro variables for data management activities

Provide some data if it doesn't work, showing the results you want to achieve.

[s_lassen · Accepted solution]

I think something lke this may work:

/* Get the unique exception codes and store them in macro variable exceptions */
%local Exceptions Exception i;
proc sql noprint;
   select distinct EXCEPTION_CD
   into :exceptions separated by ' '
   from REWARDS.REGISTRATION
   where not missing(EXCEPTION_CD)
   order by 1
   ;
quit;


%put Exceptions in file: &exceptions;

/* Delete macro symbols from previous run, if any, there can be up to 99 exception codes */
%do i=1 %to 99;
  %if %symexist(cnd_cd_exc&i) %then
    %symdel cnd_cd_exc&i;
  %if %symexist(cnd_grp_cd_exc&i) %then
    %symdel cnd_grp_cd_exc&i;
  %if %symexist(exc_type&i) %then
    %symdel exc_type&i;
  %end;

/* Loop through the number of exceptions and stored the candidate codes, candidate group codes and exception types associated to each exception code
           in macro variables */
%do i = 1 %to &sqlobs;
   %let exception=%scan(&exceptions,&i);
   %global cnd_cd_exc&exception cnd_grp_cd_exc&exception exc_type&exception;
   proc sql noprint;
     select distinct quote(CND_CD), quote(CND_GRP_CD), EXCEPTION_TYPE
     into :cnd_cd_exc&exception separated by ',', :cnd_grp_cd_exc&exception separated by ',', :exc_type&exception
     from REWARDS.REGISTRATION
     where EXCEPTION_CD = "&exception";
     quit;
   %put codes: &&cnd_cd_exc&exception;
   %put grp_cd: &&cnd_grp_cd_exc&exception;
   %put types: &&exc_type&exception;
   %end;

I put in a step to delete previous values, in case you run the code more than once in the same session.

[rahulp]

This is perfect. Applied the logic you presented and it worked like a charm.

Major and much thanks for the solution @s_lassen , without your help, my project would have been against the wall and would have required major modifications and time.

Thanks.