topic Dynamic way to handle multiple conditions with macro variables in SAS Programming

Dynamic way to handle multiple conditions with macro variables

David_Billa — Thu, 19 Dec 2019 09:28:54 GMT

Re: Dynamic way to handle multiple conditions with macro variables

PaigeMiller — Wed, 18 Dec 2019 13:05:49 GMT

So ENT_CUR_VLU1 has commas in it. When %SCAN sees a comma, it thinks it has come to the end of the argument, then next argument is the values after the comma, and that doesn't make sense in this case.

You need to mask the comma in your macro variable, as follows:

%scan(%bquote(&ENT_CUR_VLU1.,1))

and then %SCAN will think the comma is simply text instead of thinking it is the end of the argument.

In the future, please paste the entire log of the SQL step (not just the error messages) into the window that appears when you click on the {i} icon. Just showing the error messages disconnected from the code (as you have done) makes it much harder to figure out where the problem has occurred.

Re: Dynamic way to handle multiple conditions with macro variables

Kurt_Bremser — Wed, 18 Dec 2019 11:55:03 GMT

You still suffer from extreme Macro-itis.

Create a format from the data, and use a simple put statement:

data lookup;
input (incode outcode) (:$9.);
fmtname = 'myfmt';
type = "C";
if _n_ = 1
then do;
  start = 'Other';
  label = '***';
  hlo = 'O';
  output;
end;
start = substr(incode,1,4);
label = substr(outcode,6);
hlo = '';
output;
keep fmtname type start label;
datalines;
1234_EUR 5432_PLN
5678_EUR 5432_PLN
8901_EUR 5432_PLN
;

proc format cntlin=lookup;
run;

data have;
input unit $ currency $;
datalines;
1234 XXX
5678 YYY
0000 GBP
;

proc sql;
create table want as
select
  unit,
  case
    when put(unit,$myfmt.) = '***'
    then currency
    else put(unit,$myfmt.)
  end as currency
from have;
quit;

proc print data=want noobs;
run;

Result:

unit    currency

1234      PLN   
5678      PLN   
0000      GBP