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
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.
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.
- Cheers -
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.
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.
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!
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.