BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mgilbert
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
mgilbert
Obsidian | Level 7

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

Reeza
Super User
Try removing the quotation marks from the macro variables.

max(min(&varname.,&varname._p75...
mgilbert
Obsidian | Level 7

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

Reeza
Super User

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);
mgilbert
Obsidian | Level 7

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

 

 

Reeza
Super User
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 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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