topic Re: Macro variable not resolving in SAS Programming

Macro variable not resolving

Ujjawal — Thu, 02 Jul 2015 15:13:21 GMT

Hi Experts,

I am building a macro in which i am trying to collapse categories of a categorical variable. All the categories of a variable having less than 5% would be combined into one and name the label with their categories names' separated by"_". The problem arises in IF THEN statement using macro variables.

proc freq data=def;

table DSTRCT_Code / out= abcd;

run;

PROC SQL NOPRINT;

Select catt("'",DSTRCT_Code, "'") INTO : charvarname separated by ","

from abcd

having PERCENT <= 0.01 and DSTRCT_Code is not null;

QUIT;

PROC SQL NOPRINT;

Select DSTRCT_Code INTO : charvarname2 separated by "_"

from abcd

having PERCENT <= 0.01 and DSTRCT_Code is not null;

QUIT;

%put &charvarname.;

%put &charvarname2.;

options symbolgen mlogic;

data def;

length DSTRCT_Code_NEW $32.;

set def;

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = &charvarname2.;

else DSTRCT_Code_NEW = DSTRCT_Code;

run;

The above highlighted code returns missing values in DSTRCT_Code. Also i need to make sure if the variable name exceeds 32 characters, it should truncate macro variable upto 32 characters. For example, a variable named" District_Code" having categories A, B and C needs to be combined. So the new variable would be created and named "District_code_NEW" would have a category - "A_B_C" against A, B and C categories of District_Code. Otherwise, same value.

Thanks in anticipation!

Re: Macro variable not resolving

RW9 — Thu, 02 Jul 2015 15:50:28 GMT

Hi,

Provide some test data, in the form of a datastep, and some required output. Personally I never recommend putting lists of values in macro variables due to limitations and the code needed to deal with these.

Re: Macro variable not resolving

Scott_C_Moore — Thu, 02 Jul 2015 15:53:37 GMT

My guess is that you aren't selecting any values into your macro variables. The PERCENT column in the output table is not in decimal form so, 45.55% is not .4555 but 45.55. You are selecting "if <= 0.01" which would be 1% if it were in decimal form but is actually .0001% in the values that show up in the PERCENT column. I doubt you have any values that small so nothing is read into your macro variables.

Re: Macro variable not resolving

Astounding — Thu, 02 Jul 2015 16:17:20 GMT

One change you will need to make is to add double quotes:

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = "&charvarname2.";

else DSTRCT_Code_NEW = DSTRCT_Code;

It wouldn't make sense to have a SAS statement that says

then DISTRCT_CODE_NEW = A_B_C

when there is no such variable as A_B_C.

There are other issues that bear investigating. Will INTO : remove both leading and trailing blanks from the values of DSTRCT_Code? (Probably, I haven't checked.) Does PERCENT go on a scale of 0 to 1, or a scale of 0 to 100? (I think it's 0 to 100, but again I haven't checked.)

Good luck.

Re: Macro variable not resolving

Ujjawal — Thu, 02 Jul 2015 18:22:36 GMT

I have tried putting double quotes "&charvarname2." but it is not working.

NOTE: Invalid numeric data, DSTRCT_Code='RS79W'.

It seems this line - " if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = &charvarname2.;" is converting DSTRCT_Code variable into numeric.

PERCENT go on a scale of 0 to 100. I know it should be PERCENT <= 5 in my code. I was testing the code and by mistake, i put the wrong code here.

Sample District Code Field -

DSTRCT_Code

RS797

RS798

RS79W

RS79C

RS797

RS798

Re: Macro variable not resolving

Scott_C_Moore — Thu, 02 Jul 2015 18:44:30 GMT

What appears in the log for the two %put statements?

Re: Macro variable not resolving

Astounding — Thu, 02 Jul 2015 18:49:16 GMT

I suspect the conversion is happening at a slightly different place. Namely, you have continued on with the same interactive SAS session. Earlier runs that omitted the double quotes defined DSTRCT_Code_NEW as numeric within DEF. Later runs that use SET DEF are locked into having a numeric variable DSTRCT_Code_NEW.

Try starting a new SAS session and see what happens.

Re: Macro variable not resolving

Ujjawal — Thu, 02 Jul 2015 18:51:02 GMT

%put &charvarname.; and %put &charvarname2.; returns what i want.

%put &charvarname.; returns 'RS79W', 'RS79C'. And

%put &charvarname2.; returns RS79W_RS79C.

Re: Macro variable not resolving

kaade — Thu, 02 Jul 2015 19:35:23 GMT

Ujjawal,

I think it's more of a quoting issue. You need the values for the in operator to be enclosed in quotes. You can get the quotes as part of the macro variable value by formatting them with the $quote. format. Since I made up the data values, I used 25 as the limit for your conditional processing, you can change that to whatever is appropriate for your data.

Here's my code and I hope it's what you want:

data def;
input DSTRCT_Code $ ;
cards;
A
B
D
A
B
D
C
C
A
A
A
B
B
;

proc freq data=def;

table DSTRCT_Code / out= abcd;

run;

proc print data=abcd;
run;

PROC SQL ; * NOPRINT; ****I took out noprint just to make sure I was getting results;
select dstrct_code format=$quote10. into :charvarname separated by ','
from abcd

having PERCENT <= 25 and DSTRCT_Code is not null;
QUIT;
%put charvarname=&charvarname;

PROC SQL NOPRINT;

Select DSTRCT_Code INTO : charvarname2 separated by "_"

from abcd

having PERCENT <= 25 and DSTRCT_Code is not null;

QUIT;

Re: Macro variable not resolving

Ron_MacroMaven — Thu, 02 Jul 2015 20:36:16 GMT

> Also i need to make sure if the variable name exceeds 32 characters,

> it should truncate macro variable up to 32 characters

%let charvarname = %substr

(&charvarname !,1,32);

%*12345678901234567890123456789012!;

It will be obvious whatr is happening when you view this statement in monospace font

Re: Macro variable not resolving

Ujjawal — Fri, 03 Jul 2015 19:14:34 GMT

I was able to crack it. When i use KEEP option and select only the categorical variable, the code works fine. But when i run it on full dataset, it throws error. "DSTRCT_Code_NEW " has been assigned both numeric and character. In this case, it treats DSTRCT_Code as a numeric variable so it makes DSTRCT_Code_NEW as numeric variable.

The code below works fine with KEEP option.

data def1;

set def (KEEP = DSTRCT_Code) ;

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = "&charvarname2.";

else DSTRCT_Code_NEW = DSTRCT_Code;

run;

Re: Macro variable not resolving

sbb — Fri, 03 Jul 2015 19:59:15 GMT

This behavior is native to SAS system and its default type assignment being a SAS NUMERIC variable unless overridden (various techniques possible).

Also, encourage any self-initiated desk-checking with optimal diagnostic output for SAS log by using this OPTION setting:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC /* MPRINT */ ;

Scott Barry

SBBWorks, Inc.

Re: Macro variable not resolving

Astounding — Fri, 03 Jul 2015 20:14:47 GMT

You have a good solution, but the problem was never with DSTRCT_Code. It was always with DSTRCT_Code_NEW.

After you experimented with this a few times, your experiments had added a numeric variable DSTRCT_Code_NEW to the DEF data set. Later, SET DEF brought in a numeric variable DSTRCT_Code_NEW. Your programming statements were (naturally) unable to change the numeric variable into a character variable.

By adding KEEP=, you are no longer bringing in other variables. So your programming statements are able to create a DSTRCT_Code_NEW as a character variable.

Re: Macro variable not resolving

Ujjawal — Fri, 03 Jul 2015 20:31:29 GMT

Thanks a ton! This logic makes sense completely. Sure, i will check the same.