topic Re: Having Trouble with Macro Variables in SAS When Including If Then Statements in Data Step in SAS Programming

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

JackZ295 — Thu, 09 Nov 2023 13:15:23 GMT

Hi! I'm trying to create a macro that concatenates values for a variable if certain conditions are met.

My code is as follows, but the macro variables don't seem to initiate. Does anyone know what I am doing wrong? Any input regarding this would be much appreciated! Thanks so much!

%macro comb(idn, pidn, eventn, eventnum, pagen, instancen) 
data two; 
set one; 
if id=idn and pid=pidn and unique_event_name=eventn and event_id=eventnum and page=pagen and instance=instancen
then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, "&id=", idn, "&page=", pagen, "&event_id=", eventnum, "&instance=", instancen); 
else qi106=""; 
run; 
%comb("CAAA001", 3357, "consent_arm_1",48831, "cf_consent_form_documentation", 1)
%comb("CAAA001", 3357, "eligibility_arm_1",48806, "sc_screening_form", 1)

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

rudfaden — Thu, 09 Nov 2023 13:18:29 GMT

You need to resolve your macro vars

id=&idn and pid=&pidn...

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

PaigeMiller — Thu, 09 Nov 2023 13:22:11 GMT

In addition to what @rudfaden said, you have omitted a semi-colon, and you need to end the macro definition with

%mend;

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

JackZ295 — Thu, 09 Nov 2023 13:36:35 GMT

Hi @PaigeMiller , thanks for your help. Despite doing all of that, I am still getting the following errors:

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.

Is there a reason why that is happening?

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

PaigeMiller — Thu, 09 Nov 2023 13:46:11 GMT

Show us the full code you are using.

Also, run the command

options mprint;

then run your code again and show us the entire log.

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

JackZ295 — Thu, 09 Nov 2023 14:04:20 GMT

Hi @PaigeMiller , many thanks for your help. Here is the code I'm running:

/*Importing Final Data Set*/ 
options MSGLEVEL=I;
options validvarname=v7;
proc import out=one
datafile='C:\Users\JAZHU\Desktop\LHC_Links\Excel_for_SAS.xlsx'
DBMS=xlsx replace;
getnames=yes;
run;

proc print data=one (obs=10); 
run; 

proc contents data=one; 
run;
%macro comb(idn, pidn, eventn, eventnum, pagen, instancen);
data two; 
set one; 
if id=&idn and pid=&pidn and unique_event_name=&eventn and event_id=&eventnum and page=&pagen and instance=&instancen
then qi106=cats(redcap_version, "DataEntry/index.php?pid=", &pidn, "&id=", &idn, "&page=", &pagen, "&event_id=", &eventnum, "&instance=", &instancen) 
else qi106=""; 
run; 
%mend;
%comb(id, pid, unique_event_name,event_id, page, instance);

options mprint;

Basically, I am trying to create a new variable, qi106, that is a concatenation of the values of a variety of variables. This is to be done for each row in my dataset, and my dataset has 172225 rows. The variables of my dataset are as follows:

The log is here:

NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M7)
Licensed to J HPKNS UNV-BLOOMBERG SCH OF PUB HLTH DEPT OF EPID, Site 70092145.
NOTE: This session is executing on the X64_10PRO platform.

NOTE: Analytical products:

SAS/STAT 15.2
SAS/ETS 15.2
SAS/OR 15.2
SAS/IML 15.2
SAS/QC 15.2

NOTE: Additional host information:

X64_10PRO WIN 10.0.19041 Workstation

NOTE: SAS initialization used:
real time 0.94 seconds
cpu time 0.78 seconds

1 /*Importing Final Data Set*/ options MSGLEVEL=I;
2 options validvarname=v7;
3 options mprint;
4 proc import out=one
5 datafile='C:\Users\JAZHU\Desktop\LHC_Links\Excel_for_SAS.xlsx'
6 DBMS=xlsx replace;
7 getnames=yes;
8 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 296037 observations and 7 variables.
NOTE: WORK.ONE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 11.24 seconds
cpu time 11.25 seconds

9
10 proc print data=one (obs=10);
NOTE: Writing HTML Body file: sashtml.htm
11 run;

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

12
13 proc contents data=one;
14 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

15 %macro comb(idn, pidn, eventn, eventnum, pagen, instancen);
16 data two;
17 set one;
18 if id=&idn and pid=&pidn and unique_event_name=&eventn and event_id=&eventnum and page=&pagen and
18 ! instance=&instancen
19 then qi106=cats(redcap_version, "DataEntry/index.php?pid=", &pidn, "&id=", &idn, "&page=",
19 ! &pagen, "&event_id=", &eventnum, "&instance=", &instancen)
20 else qi106="";
21 run;
22 %mend;
23 %comb(id, pid, unique_event_name,event_id, page, instance);
MPRINT(COMB): data two;
MPRINT(COMB): set one;
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.
NOTE 137-205: Line generated by the invoked macro "COMB".
2 &eventnum, "&instance=", &instancen) else qi106=""; run;
----
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>,
=, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=,
|, ||, ~, ~=.

NOTE: Line generated by the invoked macro "COMB".
2 &eventnum, "&instance=", &instancen) else qi106=""; run;
----
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.

MPRINT(COMB): if id=id and pid=pid and unique_event_name=unique_event_name and event_id=event_id
and page=page and instance=instance then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid,
"&id=", id, "&page=", page, "&event_id=", event_id, "&instance=", instance) else qi106="" ;
MPRINT(COMB): run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
1:150 2:50
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TWO may be incomplete. When this step was stopped there were 0
observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Any thoughts?

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

PaigeMiller — Thu, 09 Nov 2023 14:08:33 GMT

cats(redcap_version, "DataEntry/index.php?pid=", &pidn, "&id=", &idn, "&page=", &pagen, "&event_id=", &eventnum, "&instance=", &instancen)

There is no macro variable named &id

There is no macro variable named &page

and so on.

Can you write out, without macros and without macro variables, what the line that I show above should look like for one call of the macro? In other words, if you were writing the program without macros and without macro variables, what would the line be?

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

JackZ295 — Thu, 09 Nov 2023 14:16:20 GMT

Hi @PaigeMiller , many thanks for your help. If you are referring to

"&id=", etc., those aren't macro variables. Those are strings of text.

This is what the code would look like without the macro variables. The reason why I am using a macro is because my dataset has over 100,000 observations, and I would have to write 100,000 if-then statements.

data two;
set one;
if id=CAAA001 and pid=12345 and unique_event_name=v01_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;

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

PaigeMiller — Thu, 09 Nov 2023 14:23:26 GMT

@JackZ295 wrote:

Hi @PaigeMiller , many thanks for your help. If you are referring to

"&id=", etc., those aren't macro variables. Those are strings of text.

This is what the code would look like without the macro variables. The reason why I am using a macro is because my dataset has over 100,000 observations, and I would have to write 100,000 if-then statements.

SAS thinks they are macro variables and can't find them. Why? Because you put an & in front of the variable name.

If you don't want them to be macro variables, you should not put & in front of the variable name, and then SAS will think they are not macro variables and then SAS will treat them as text.

With regard to your 100,000 observations, you have to get it to work for one observation first without macros and without macro variables. If you can't get it to work for one observation without macros and without macro variables, then it will not work if you try to turn it into a macro and use macro variables. This is such an important piece of advice I am going to put it red bold letters: get the code to work first for a small example without macros and without macro variables before trying to turn it into a macro.

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

JackZ295 — Thu, 09 Nov 2023 14:30:36 GMT

Hi @PaigeMiller , the code actually does work without the macro variables, but I think I'm subbing in the wrong variables for the macro variables somehow. It does concatenate correctly when I try it for the first observation.

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

PaigeMiller — Thu, 09 Nov 2023 14:32:45 GMT

Show me the code that actually works without macros and without macro variables.

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

JackZ295 — Thu, 09 Nov 2023 14:46:34 GMT

Hi @PaigeMiller , many thanks for your help. Here is the code that works for the first observation of my dataset:

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;

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

Quentin — Thu, 09 Nov 2023 14:48:46 GMT

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.

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

PaigeMiller — Thu, 09 Nov 2023 14:48:50 GMT

It can't possibly work, it refers to a macro variable &id. And in any event, I keep asking for code without macros and without macro variables, as if the problem were only to be solved for one line, and so no need for macros and no need for macro variables.

Show me the log for this data step (all of the log for this data step) when you run this code.

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

Quentin — Thu, 09 Nov 2023 14:58:38 GMT

I think that step can only work if you are ignoring messages in your log about unresolved macro variables.

Does this step work for you:

data two;
set one (obs=5);  *Limit input data, only use obs=5 if you know the records for the if statement are in the first 5 records;
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="";

put (id pid unique_event_name event_id page instance redcap_version qi106)(=);
run;

In the above, I used single quotes to prevent the macro processor from trying to resolve macro variables ID, Page, Event_ID, and Instance. This code has no macro variable references.

If this code works, please show an example of code that works with two IF statements, and show the full log from running the step so we can see the values you want to generate for qi106.

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

JackZ295 — Thu, 09 Nov 2023 15:05:13 GMT

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.

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

JackZ295 — Thu, 09 Nov 2023 15:10:42 GMT

Hi @Quentin , many thanks for your help. I'll see if this works, but the records for the if statement are all of them. I'm trying to get the if and then statement to apply to all rows, though I guess the 'else qi106=""' statement shouldn't be there, as that would overwrite every role.

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

PaigeMiller — Thu, 09 Nov 2023 15:13:29 GMT

Most of us will not download Microsoft Excel (or any Microsoft Office) files as they can be a security threat. The proper way to provide data is via WORKING data step code (examples and instructions). However, as far as this problem is concerned, data is irrelevant at this time.

It simply does not matter that you think &id is a text string. SAS thinks it is a macro variable, and SAS doesn't know or care that you think it is a text string.

What happens if you change all the double quotes to single quotes on that line. Does this produce the desired text string in qi106?

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

JackZ295 — Thu, 09 Nov 2023 15:21:58 GMT

Hi @Quentin , many thanks for your help again. This code works, and here is the code for the first two observations of the sample dataset (see attached).

/*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);;put (id pid unique_event_name event_id page instance redcap_version qi106)(=);
if id="CAAA001" and pid=3357 and unique_event_name="eligibility_arm_1" and event_id=48806 and page="sc_screening_form" and instance=1 then qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid, '&id=', id, '&page=', page, '&event_id=', event_id, '&instance=', instance);;put (id pid unique_event_name event_id page instance redcap_version qi106)(=);
run;

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

NOTE: Analytical products:

SAS/STAT 15.2
SAS/ETS 15.2
SAS/OR 15.2
SAS/IML 15.2
SAS/QC 15.2

NOTE: Additional host information:

X64_10PRO WIN 10.0.19041 Workstation

NOTE: SAS initialization used:
real time 1.49 seconds
cpu time 0.95 seconds

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 2 observations and 7 variables.
NOTE: WORK.ONE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds

10
11
12 data two;
13 set one;
14 if id="CAAA001" and pid=3357 and unique_event_name="consent_arm_1" and event_id=48831 and
14 ! page="cf_consent_form_documentation" and instance=1 then qi106=cats(redcap_version,
14 ! "DataEntry/index.php?pid=", pid, '&id=', id, '&page=', page, '&event_id=', event_id,
14 ! '&instance=', instance);;put (id pid unique_event_name event_id page instance redcap_version
14 ! qi106)(=);
15 if id="CAAA001" and pid=3357 and unique_event_name="eligibility_arm_1" and event_id=48806 and
15 ! page="sc_screening_form" and instance=1 then qi106=cats(redcap_version,
15 ! "DataEntry/index.php?pid=", pid, '&id=', id, '&page=', page, '&event_id=', event_id,
15 ! '&instance=', instance);;put (id pid unique_event_name event_id page instance redcap_version
15 ! qi106)(=);
16 run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column).
Truncation can result.
14:148 qi106
id=CAAA001 pid=3357 unique_event_name=consent_arm_1 event_id=48831 page=cf_consent_form_documentation
instance=1 redcap_version=url
qi106=urlDataEntry/index.php?pid=3357&id=CAAA001&page=cf_consent_form_documentation&event_id=48831&ins
tance=1
id=CAAA001 pid=3357 unique_event_name=consent_arm_1 event_id=48831 page=cf_consent_form_documentation
instance=1 redcap_version=url
qi106=urlDataEntry/index.php?pid=3357&id=CAAA001&page=cf_consent_form_documentation&event_id=48831&ins
tance=1
id=CAAA001 pid=3357 unique_event_name=eligibility_arm_1 event_id=48806 page=sc_screening_form
instance=1 redcap_version=url qi106=
id=CAAA001 pid=3357 unique_event_name=eligibility_arm_1 event_id=48806 page=sc_screening_form
instance=1 redcap_version=url
qi106=urlDataEntry/index.php?pid=3357&id=CAAA001&page=sc_screening_form&event_id=48806&instance=1
NOTE: There were 2 observations read from the data set WORK.ONE.
NOTE: The data set WORK.TWO has 2 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.06 seconds

17
18 proc print data=two (obs=2);
NOTE: Writing HTML Body file: sashtml.htm
19 run;

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

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

Quentin — Thu, 09 Nov 2023 15:38:02 GMT

From that example, it's not clear if you need an IF statement at all. It looks like you are generating the same string for both pids.

What would you think of:

data two;
set one;
qi106=cats(redcap_version, "DataEntry/index.php?pid=", pid, '&id=', id, '&page=', page, '&event_id=', event_id, '&instance=', instance);
put (id pid unique_event_name event_id page instance redcap_version qi106)(=);
run;