DATA Step, Macro, Functions and more

Macro variable not resolving

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Macro variable not resolving

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!


Accepted Solutions
Solution
‎07-03-2015 04:14 PM
Super User
Posts: 5,509

Re: Macro variable not resolving

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. 

View solution in original post


All Replies
Super User
Super User
Posts: 7,958

Re: Macro variable not resolving

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.

Occasional Contributor
Posts: 12

Re: Macro variable not resolving

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.

Super User
Posts: 5,509

Re: Macro variable not resolving

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.

Regular Contributor
Posts: 183

Re: Macro variable not resolving

Posted in reply to Astounding

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

RS797

RS797

RS798

RS798

Occasional Contributor
Posts: 12

Re: Macro variable not resolving

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

Regular Contributor
Posts: 183

Re: Macro variable not resolving

Posted in reply to Scott_C_Moore

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

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

%put &charvarname2.; returns RS79W_RS79C.

Super User
Posts: 5,509

Re: Macro variable not resolving

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.

SAS Employee
Posts: 7

Re: Macro variable not resolving

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;

Regular Contributor
Posts: 227

Re: Macro variable not resolving

> 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

Regular Contributor
Posts: 183

Re: Macro variable not resolving

Posted in reply to Ron_Fehd_macro_maven

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;

Super Contributor
Super Contributor
Posts: 3,174

Re: Macro variable not resolving

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.

Solution
‎07-03-2015 04:14 PM
Super User
Posts: 5,509

Re: Macro variable not resolving

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. 

Regular Contributor
Posts: 183

Re: Macro variable not resolving

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 698 views
  • 0 likes
  • 7 in conversation