Having Trouble with Macro Variables in SAS When Including If Then Statements in Data Step

[Quentin]

Agree with Paige.  If you can show the code that works (without macro variables), hopefully along with a small amount of sample data (like data step with CARDS statement to generate 5 rows of data), then we can help you with figuring out how to write a macro to generate that code.  Without that, we're guessing.

That said, I made a little example that does *something* similar to what you are asking. I don't think this is the answer, but it is the kind of thing you are doing.  Note one reason that this is not the answer is that if you call this macro multiple times, it will overwrite the work.two each time.

data one ;
  input id $7. pidn redcap_version $3. ;
  cards ;
CAAA001 3357 foo 
CAAA002 3358 bar 
;


%macro comb(idn, pidn);
data two; 
  set one; 
  if id=&idn
  then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', &idn); 
  else qi106=""; 

  put qi106= ;
run; 
%mend ;

%comb("CAAA001", 3357)

This returns:

qi106=fooDataEntry/index.php?pid=3357&id=CAAA001
qi106=

But really, if you can give a small amount of sample input data, and describe the output data you want (a new variable with a string with the value .... ), people will be able to help you more.  It's rare that you need to write a macro that runs hundreds of times.  It could be a case where you could use something more basic, like a merge or SQL join.

[JackZ295]

Hi @Quentin and @paige2 , many thanks for your help. However, @PaigeMiller , as I was trying to mention, the quoted string that you see isn't a macro variable. It's a quoted string that I'm trying to concatenate with other variables. I will provide a row of sample data with some modifications (modifying the URL as it does lead to a real url). 

Here is the code: 

/*Importing Final Data Set*/ 
options MSGLEVEL=I;
options validvarname=v7;

proc import out=one
datafile='C:\Users\JAZHU\Desktop\LHC_Links\Sample.xlsx'
DBMS=xlsx replace;
getnames=yes;
run;



data two;
set one;
if id="CAAA001" and pid=3357 and unique_event_name="consent_arm_1
" and event_id=48831 and page="cf_consent_form_documentation" and instance=1
then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid, "&id=", id, "&page=", page, "&event_id=", event_id, "&instance=", instance);
else qi106="";
run;


proc print data=two (obs=1); 
run; 

Please note this is just for one row. It would also work for the other rows, but the values for id, pid, unique_event_name, event_id, page, and instance would differ. 

1 /*Importing Final Data Set*/
2 options MSGLEVEL=I;
3 options validvarname=v7;
4
5 proc import out=one
6 datafile='C:\Users\JAZHU\Desktop\LHC_Links\Sample.xlsx'
7 DBMS=xlsx replace;
8 getnames=yes;
9 run;

NOTE: VARCHAR data type is not supported by the V9 engine. Variable id has been converted to CHAR
data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable unique_event_name has been
converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable page has been converted to CHAR
data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable redcap_version has been converted
to CHAR data type.
NOTE: The import data set has 1 observations and 7 variables.
NOTE: WORK.ONE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

10
11
12
13 data two;
14 set one;
15 if id="CAAA001" and pid=3357 and unique_event_name="consent_arm_1
16 " and event_id=48831 and page="cf_consent_form_documentation" and instance=1
17 then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid, "&id=", id, "&page=", page,
17 ! "&event_id=", event_id, "&instance=", instance);
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference PAGE not resolved.
WARNING: Apparent symbolic reference EVENT_ID not resolved.
WARNING: Apparent symbolic reference INSTANCE not resolved.
18 else qi106="";
19 run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column).
Truncation can result.
17:6 qi106
NOTE: There were 1 observations read from the data set WORK.ONE.
NOTE: The data set WORK.TWO has 1 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

20
21
22 proc print data=two (obs=1);
NOTE: Writing HTML Body file: sashtml.htm
23 run;

NOTE: There were 1 observations read from the data set WORK.TWO.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.27 seconds
cpu time 0.15 seconds

I do see in the log that there's that issue of SAS recognizing my text string as a macro variable. Not sure if there's a workaround for it. The text string is part of a URL. 

Attached you will find a sample dataset. 

[ballardw]

First, I am not going to claim to understand why you think you might  need 100,000 if then and why you think that moving them to a macro is any simpler.

This has a very STUPID element:

if id="CAAA001" and pid=3357 and unique_event_name="consent_arm_1
" and event_id=48831 and page="cf_consent_form_documentation" and instance=1
then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid, "&id=", id, "&page=", page, "&event_id=", event_id, "&instance=", instance);
else qi106="";

Since that cats will execute for only one value of ID then including the Id variable is a waste of time.

As are all the highlighted elements. If you have the values in the "if" put the value in the  Cats (and that could be one long fixed string)

One suspects that we may have a case of the XY problem where the actual PROBLEM to solve has not be adequately described and attempting to "fix" an approach that is broken to begin with may be a lot of misdirected energy.

Note: Since you are importing data from XLSX file this is a fragile approach in general. The file you might need this for, if any, might change the types for the variables.

The character &  is a macro processor trigger, when encountered SAS thinks that you are attempting to use a MACRO VARIABLE named with the text following. IF that is not your intent then do not place such characters inside double quotes. Single quotes will tell SAS that you do not intend to use the value as a macro reference.

[JackZ295]

---

@Quentin wrote:

Agree with Paige.  If you can show the code that works (without macro variables), hopefully along with a small amount of sample data (like data step with CARDS statement to generate 5 rows of data), then we can help you with figuring out how to write a macro to generate that code.  Without that, we're guessing.

That said, I made a little example that does *something* similar to what you are asking. I don't think this is the answer, but it is the kind of thing you are doing.  Note one reason that this is not the answer is that if you call this macro multiple times, it will overwrite the work.two each time.

data one ;
  input id $7. pidn redcap_version $3. ;
  cards ;
CAAA001 3357 foo 
CAAA002 3358 bar 
;


%macro comb(idn, pidn);
data two; 
  set one; 
  if id=&idn
  then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', &idn); 
  else qi106=""; 

  put qi106= ;
run; 
%mend ;

%comb("CAAA001", 3357)

This returns:

qi106=fooDataEntry/index.php?pid=3357&id=CAAA001
qi106=

But really, if you can give a small amount of sample input data, and describe the output data you want (a new variable with a string with the value .... ), people will be able to help you more.  It's rare that you need to write a macro that runs hundreds of times.  It could be a case where you could use something more basic, like a merge or SQL join.

---

Hi @Quentin , this is close to what I'm looking for. However, is there a reason why qi106 is blank for the second record? Is there a way to get it to appear? What if you have more records?

[JackZ295]

---

@JackZ295 wrote:

---

@Quentin wrote:

Agree with Paige.  If you can show the code that works (without macro variables), hopefully along with a small amount of sample data (like data step with CARDS statement to generate 5 rows of data), then we can help you with figuring out how to write a macro to generate that code.  Without that, we're guessing.

That said, I made a little example that does *something* similar to what you are asking. I don't think this is the answer, but it is the kind of thing you are doing.  Note one reason that this is not the answer is that if you call this macro multiple times, it will overwrite the work.two each time.

data one ;
  input id $7. pidn redcap_version $3. ;
  cards ;
CAAA001 3357 foo 
CAAA002 3358 bar 
;


%macro comb(idn, pidn);
data two; 
  set one; 
  if id=&idn
  then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', &idn); 
  else qi106=""; 

  put qi106= ;
run; 
%mend ;

%comb("CAAA001", 3357)

This returns:

qi106=fooDataEntry/index.php?pid=3357&id=CAAA001
qi106=

But really, if you can give a small amount of sample input data, and describe the output data you want (a new variable with a string with the value .... ), people will be able to help you more.  It's rare that you need to write a macro that runs hundreds of times.  It could be a case where you could use something more basic, like a merge or SQL join.

---

Hi @Quentin , this is close to what I'm looking for. However, is there a reason why qi106 is blank for the second record? Is there a way to get it to appear? What if you have more records?

---

Hi @Tom and @ballardw , this is close to what I'm looking for, but is there a way to write a macro such that the cats function is run more than once and so that the results aren't overwritten?

[Tom]

Remove the IF statement?

data one ;
  input id $7. pidn redcap_version $3. ;
  cards ;
CAAA001 3357 foo 
CAAA002 3358 bar 
;
data two; 
  set one; 
  length QI106 $100;
  qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', id); 
run; 

[Quentin]

Yes, that was my suggestion earlier too.  Remove the IF statement.  Did you see  this message?  Did you try it?

https://communities.sas.com/t5/SAS-Programming/Having-Trouble-with-Macro-Variables-in-SAS-When-Inclu...

[JackZ295]

Thanks for your help. However, in that case, the conditional logic isn't there, and we do need to conditional logic. This code would indeed help to generate all of the possible links. However, we are trying to write code that does include conditional logic as we want to generate these links in another dataset where only some of the IDs in my dataset are affected. I am essentially creating a master sheet of possible links only so that the coding for generating those links is there. 

You may say that I could just merge the link with the appropriate record ID in that dataset. However, each ID has hundreds of unique links, depending on the situation and the components of that record (e.g. unique_event_name, event_id, page, etc.). 

Does that make sense? Is there a way to avoid overwriting even with the conditional logic @Quentin ?

[ballardw]

So you in effect are trying to see if some variables in one data set correspond to  value(s) in another?

There are lots of examples of that that do not require trying to recreate long complex (conditional, still not understanding why If is needed)

From what you are attempting to build I think I would start with the other data set and extract the values for variables that you want to match on. Then use a data step merge (after appropriate sorts) or SQL join.

But since you not provided much in the way of data you have or actual values you expect to match can't show an applicable example.

[Tom]

I don't think you have explained what you are trying to do very well.

If one existing record can be used to generate multiple different URL's then you probably need to design for that.  It might be better to generate multiple observations than generate multiple variables.

For example if there are three different links you can make from each input record you might want to do something like this:

data want;
  set have;
  length link_type $20 link $300 ;
  link_type='unique_event_name';
  link=cats(.....);
  output;
  link_type='event_id';
  link=cats(.....);
  output;
  link_type='page';
  link=cats(....);
  output;
run;

If not all observations should generate all three link types then you could then make those individual blocks conditional on something.

[Tom]

If ID is not a macro variable then do not have &ID or "&ID" in your code.

If you want to generate &ID as text then enclose it in single quotes and the macro processor will ignore the value.

cats(.....,'&id=',&idn,...)

But as others have said if you can explain the WHOLE PROBLEM and not just the issue with this one technique perhaps you can get a better answer.

Since it looks like you are trying to generate a URL can you explain what you intend to do with the URL?  Are you trying to write a webpage with embedded links?  Are you going to use the URL yourself to retrieve data?