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

[JackZ295]

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)

[rudfaden]

You need to resolve your macro vars

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

[PaigeMiller]

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

%mend;

[JackZ295]

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? 

[PaigeMiller]

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.

[JackZ295]

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? 

[PaigeMiller]

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?

[JackZ295]

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;

[PaigeMiller]

---

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

[JackZ295]

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. 

[PaigeMiller]

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

[JackZ295]

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; 

[PaigeMiller]

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.

[Quentin]

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.

[JackZ295]

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.