Help using Base SAS procedures

Referencing / Parsing Concatenated Macro Variables

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Referencing / Parsing Concatenated Macro Variables

[ Edited ]

Have taken a look at similar threads on the boards (here and here), and also at this Sugi paper, but cannot get the code to work.

 

I have my original data set, and versions of each of the original variables stored as global macro variables (via CALL SYMPUT) at various percentiles. So for the variable TARGET_WINS in the parent data set, global macro variables named TARGET_WINS_P1, TARGET_WINS_P25, etc.. also exist with the correct corresponding values.

 

My goal is, for each of the variables in the original data set, append it with these newly created trimmed / truncated variables (while preserving the orignal variables and values). I'm getting stuck having SAS correctly parse the variable name in the original data set, and add the correct percentile exentsion to make a concatenated version of the variable, which it then pulls from global (the macro variables are correct and were verified with %put _user_).

 

The code below will execute, but it's not parsing how I intended. The problem seems to be with getting SAS to correctly parse the concatenated macro variables: for the variable TARGET_WINS, create TARGET_WINS_T75 that uses the value stored in the global macro variable of TARGET_WINS_P75 and TARGET_WINS_P25. I think I'm just getting the syntax of "&varname._P75" incorrect. I've tried multiple iterations, but nothing. The log also guides me to that conclusion.

 

*	Proc contents;
proc contents data = &data_og. out = &contents.;
run; quit;

*	Drop unnecessary variables gained from PROC CONTENTS;
data &contents.;
	set &contents.(keep = name type length varnum format formatl
		informat informl just npos nobs);
run; quit;

*	Clone data set for append;
data &data_trim.;
	set &data_og.;
run; quit;

*	Trim Macro;
%macro trim(varname);
	data &data_trim.;
		set &data_trim.;
			&varname._T75	=	max(min(&varname.,"&varname._P75"),"&varname._P25");
			&varname._T90	=	max(min(&varname.,"&varname._P90"),"&varname._P10");
			&varname._T95	=	max(min(&varname.,"&varname._P95"),"&varname._P5");
			&varname._T99	=	max(min(&varname.,"&varname._P99"),"&varname._P1");
	run; quit;
%mend;

*	Append the data set;
data _null_;
	do i = 1 to num;
		set &contents. nobs = num;
			call execute('%trim('||name||');');
	end;
run; quit;	

Since there's so many macro variables, here's a non-macro version of what I'm trying to accomplish:

 

data TEST_123;
	set &data_trim.;
	Index_T95 = max(min(INDEX,&INDEX_P95.),&INDEX_P5.);
run; quit;

I'm just trying to automate doing that for each variable through a macro. The variables "&INDEX_P95." and "&INDEX_P5." are stored as global macro variables with values.

 

Any thoughts are much appreciated! I have been working at this for far too long and want to bash my head on the keyboard...

 

Michael


Accepted Solutions
Solution
‎10-12-2015 11:21 PM
Super User
Posts: 17,770

Re: Referencing / Parsing Concatenated Macro Variables

[ Edited ]

Remove the quotations and add two more ampersands.

You first ampersand resolves the macro variable to the variable name and the next two tell it to resolve to the actual macro variable.

Assume varname=cats and cats_p25 = 25:

&varname_p25 =>  cats_p25 

&&&varname_p25 =>  25

&varname._T75 = max(min(&varname.,&&&varname._P75),&&&varname._P25);
&varname._T90 = max(min(&varname.,&&&varname._P90),&&&varname._P10);
&varname._T95 = max(min(&varname.,&&&varname._P95),&&&varname._P5);
&varname._T99 = max(min(&varname.,&&&varname._P99),&&&varname._P1);

View solution in original post


All Replies
Contributor
Posts: 20

Re: Referencing / Parsing Concatenated Macro Variables

[ Edited ]

Updated code above where issue occurs. By using the double-quotes around the global macro variable name (e.g. "&varname._P75"), the resulting data set is correctly appended with the right variable names (before it included the P and T versions, now just the T), however, the data still seem not to be pulling from the global macro variable value.

 

For example, after running the code, a variable TARGET_WINS_T75 is created in the data set, but it appears to only be a clone of TARGET_WINS, rather than a clone of TARGET_WINS with trimmed max values = TARGET_WINS_P75 and trimmed min values = TARGET_WINS_P25.

 

For what it's worth, the log says "Invalid numeric data, '[VAR_NAME_PXX]'" where PXX = percentile. So it seems SAS is still not pulling in the value of the global macro variable. Perhaps that's why it's only cloned?

 

The face palms continue until the code executes correctly...

Super User
Posts: 17,770

Re: Referencing / Parsing Concatenated Macro Variables

Try removing the quotation marks from the macro variables.

max(min(&varname.,&varname._p75...
Contributor
Posts: 20

Re: Referencing / Parsing Concatenated Macro Variables

Tried that, but the resulting data set is not pulling in the correct values. For instance, TARGET_WINS_T75 should have a max value = P75, but the max value = the original value.

 

I've been reading A LOT of posts, and I think it's related to local vs. global scoping of macro variables? So the thought is that the local variables &varname. works fine, but SAS doesn't know that when I say &varname._P75, I want it to look in global table? I tried using SYMPUTX( ,'l') to force them into the local table, but they don't show up via %put _LOCAL_, and indeed show up in %put _GLOBAL_

 

The other thought I had was something like the below to tell SAS to look at the global table rather than the local one, but similarly cannot get this to execute correctly...

 

max(min(&varname.,%global &varname._p75...

Solution
‎10-12-2015 11:21 PM
Super User
Posts: 17,770

Re: Referencing / Parsing Concatenated Macro Variables

[ Edited ]

Remove the quotations and add two more ampersands.

You first ampersand resolves the macro variable to the variable name and the next two tell it to resolve to the actual macro variable.

Assume varname=cats and cats_p25 = 25:

&varname_p25 =>  cats_p25 

&&&varname_p25 =>  25

&varname._T75 = max(min(&varname.,&&&varname._P75),&&&varname._P25);
&varname._T90 = max(min(&varname.,&&&varname._P90),&&&varname._P10);
&varname._T95 = max(min(&varname.,&&&varname._P95),&&&varname._P5);
&varname._T99 = max(min(&varname.,&&&varname._P99),&&&varname._P1);
Contributor
Posts: 20

Re: Referencing / Parsing Concatenated Macro Variables

THANK YOU! That nailed it! So awesome. I tried a few combinations with double ampersands (before / after), added periods, and so on, but never the three in front.

 

Do you know of a good resource to help understand when to add additional ampersands or periods, either to the front or in the middle of macro variables? Some other combinations I tried were (without the quotes): "&&varname.&_P75." or "&&varname&_P75..." based on what I could find online. Just having a hard time understanding when each of those may be appropriate (if ever).

 

Thank you again, this is great.

 

Michael

 

 

Super User
Posts: 17,770

Re: Referencing / Parsing Concatenated Macro Variables

Glad it worked. Unfortunately I don't have a good reference. One suggestion though - look at the the log. You were getting a NOTE about a variable that didn't exist that hinted that the macro variable wouldn't resolve. When faced with this situation I usually add ampersands until it resolves Smiley Wink
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 403 views
  • 1 like
  • 2 in conversation