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

[PaigeMiller]

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?

[JackZ295]

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: 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 1.49 seconds
cpu time 0.95 seconds

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

[Quentin]

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;

?

[JackZ295]

Hi @Quentin , many thanks for your help. However, we are trying to generate a master spreadsheet with a comprehensive list of links (qi106) - over 100,000 possibilities. We are launching data quality queries, and another programmer has a dataset with only select IDs being affected, so we want to write code that includes conditional logic (similar to how I have it), such that the links are appropriately matched to the appropriate record. In my example, those two links are very different. If you see the strings, the unique_event_name are different for the same ID. 

Each ID possibly has many links attached to them. A simple merge would not be possible, as SAS only merges by one variable, not multiple. If I simply merge by ID, the link might not be appropriately paired with the record, if that makes sense. It's a little hard to explain, but feel free to ask me questions if something doesn't make sense. Is it possible to use macros for my situation?

[Quentin]

Hi,

I've read a few more of these posts, but getting more and more confused as to your goal. : )

The best way to illustrate your goal is with an example.  So far, you have shared the following example data:

data have ;
  input id : $10. pidn unique_event_name :$20. event_id page :$20. redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1      48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1  48806 sc_screning_form url 1
;

In terms of understanding your goal, I think you want to read in that data, and use it to generate a URL of some sort, written to the variable QI106.

This code will generate one URL for each of your records:

data want; 
  set have; 
  length QI106 $200;
  qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', id, '&page=', page, '&event_id=', event_id, '&instance=', instance);
  put (_N_ qi106)(=);
run;

Note that you don't need an IF statement at all.  It will read each of your records, and generate a new character value for qi106 for each record.

It will generate:

_N_=1 QI106=urlDataEntry/index.php?pid=3357&id=CAAA001&page=cf_consent_form&event_id=48831&instance=1
_N_=2 QI106=urlDataEntry/index.php?pid=3357&id=CAAA001&page=sc_screning_form&event_id=48806&instance=1

Note those are two different URLs, with the values in the URLs coming from the input data.

I'm guessing this may not be what you want.  But I'm not clear on what you do want.

For this example input data you provided, what would you want to generate as output data?

Or if it would be helpful to provide different example input data, feel free to do so.  Please provide it as a CARDS data step like I did, not as Excel. 

If you can provide a small amount of sample input data, and also provide the output data you would want to generate, we can most likely help you get there.

[Patrick]

---

Each ID possibly has many links attached to them. A simple merge would not be possible, as SAS only merges by one variable, not multiple. 

---

@JackZ295  From what I understand you've got a master table with multiple rows per id and you 've got a lookup table with one row per id. You only want to select rows from the master table that have an id that also exists in the lookup table. 

If above is not true then please provide sample data for both of these tables and show us the desired result.

Once you've got a table with only the desired rows then you want to construct a string (the url) with multiple parameters as key/value pairs concatenated via an ampersand.

NOTE: When constructing a string you need to provide the ampersand in single quotes as else SAS will interpret it as a macro token. For example if you want to construct a string that includes &id=... then &id needs to be in single quotes as else SAS will interpret this string as macro variable &id and try to resolve it.

If I understood correctly what you have and what you want then I believe code as below should do.

data have ;
  input id : $10. pidn unique_event_name :$20. event_id page :$20. redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1      48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1  48806 sc_screning_form url 1
CXXX001 3357 eligibility_arm_1  48806 sc_screning_form url 1
;

data id_to_select(label="List of id's to select");
  input id : $10.;
  datalines;
CAAA001
;

data generate_links(keep=qi106:);
  merge have(in=a) id_to_select(in=b);
  by id;

  /* only select matching rows */
  if a and b;

  /* create url's */
  length qi106 $200;
  qi106=cats(
                redcap_version, 'DataEntry/index.php?pid=', pidn
              , '&id=', id
              , '&page=', page
              , '&event_id=', event_id
              , '&instance=', instance
            );

  length qi106_v2 $200;
  qi106_v2=cats(
                redcap_version, 'DataEntry/index.php?pid=', pidn
              , '&id=', id
              , '&page=', page
              , '&event_id=', urlencode(strip(event_id))
              , '&instance=', urlencode(strip(instance))
            );
run;

proc print data=generate_links;
run;

For constructing the string I would recommend to use SAS function urlencode() for source strings that could potentially contains characters with special meaning in a URL.

And just as a thought:

If you plan to use these URL's for issuing 100000+ REST calls then be aware that this not only might take rather long to run but that the remote server also might consider that many queries within a short time frame as a DoS attack and start to refuse requests. 

[JackZ295]

---

@Patrick wrote:

---

Each ID possibly has many links attached to them. A simple merge would not be possible, as SAS only merges by one variable, not multiple. 

---

@JackZ295  From what I understand you've got a master table with multiple rows per id and you 've got a lookup table with one row per id. You only want to select rows from the master table that have an id that also exists in the lookup table. 

If above is not true then please provide sample data for both of these tables and show us the desired result.

Once you've got a table with only the desired rows then you want to construct a string (the url) with multiple parameters as key/value pairs concatenated via an ampersand.

NOTE: When constructing a string you need to provide the ampersand in single quotes as else SAS will interpret it as a macro token. For example if you want to construct a string that includes &id=... then &id needs to be in single quotes as else SAS will interpret this string as macro variable &id and try to resolve it.

If I understood correctly what you have and what you want then I believe code as below should do.

data have ;
  input id : $10. pidn unique_event_name :$20. event_id page :$20. redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1      48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1  48806 sc_screning_form url 1
CXXX001 3357 eligibility_arm_1  48806 sc_screning_form url 1
;

data id_to_select(label="List of id's to select");
  input id : $10.;
  datalines;
CAAA001
;

data generate_links(keep=qi106:);
  merge have(in=a) id_to_select(in=b);
  by id;

  /* only select matching rows */
  if a and b;

  /* create url's */
  length qi106 $200;
  qi106=cats(
                redcap_version, 'DataEntry/index.php?pid=', pidn
              , '&id=', id
              , '&page=', page
              , '&event_id=', event_id
              , '&instance=', instance
            );

  length qi106_v2 $200;
  qi106_v2=cats(
                redcap_version, 'DataEntry/index.php?pid=', pidn
              , '&id=', id
              , '&page=', page
              , '&event_id=', urlencode(strip(event_id))
              , '&instance=', urlencode(strip(instance))
            );
run;

proc print data=generate_links;
run;

For constructing the string I would recommend to use SAS function urlencode() for source strings that could potentially contains characters with special meaning in a URL.

And just as a thought:

If you plan to use these URL's for issuing 100000+ REST calls then be aware that this not only might take rather long to run but that the remote server also might consider that many queries within a short time frame as a DoS attack and start to refuse requests. 

---

Hi @Patrick , many thanks for your help. That is very close to what I am looking for. However, the lookup table doesn't necessarily have one row per ID. Essentially, my team created a REDCap database for flagging data quality queries. It consists of two forms: an initiation form that our team fills out, either through a CSV upload generated by R programming (another programmer is going to translate my SAS programming into R, as I'm more familiar with SAS) or by manually filling in the form. The initiation form contains variable such as id, pid, unique_event_name, event_id, page, etc.  Each row of the CSV of the initiation form contains data for one participant, though there can be many rows per participant, as there are a variety of visit forms. The variable, page, can vary a lot, even among IDs, as each participant fills out many forms, and page represents the value that identifies those forms. I did indeed create a separate master spreadsheet with all of the possible combinations of id, pid, unique_event_name, etc. so that I could write comprehensive conditional (if-then) code to generate links that lead to the relevant records and forms. This does not mean that all 100,000+ links will be used or generated. Whenever we generate CSV initiation forms using R or SAS code, we want to run the comprehensive code for generating links (yes, 100,000+ links would be generated if 100,000+ sets of conditions were met, but we are running this code on a data set of perhaps 100-200 records at a time) so that we can ensure that the correct link is generated without fail. I was trying to facilitate the creation of this code with macros, rather than writing 100,000 lines of if-then logic, but is that possible? Would arrays and do-looping help? Here is some sample data below: 

Initiation CSV dataset

data initiation ;
  input id : $10. pidn unique_event_name :$20. event_id page :$30. 
  redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1 48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1 48806 sc_screening_form url 1
CAAA001 3357 medication_arm_1 38416 cm_medication_use_log url 1
CABA001 3357 v01_arm_1 48807 pi_participant_information url 1 
CACA001 3357 v01_arm_1 48807 ba_baseline_demographics url 1 
;

Master Sheet Data Set 

data master ;
  input id : $10. pidn unique_event_name :$20. event_id page :$30. 
  redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1 48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1 48806 sc_screening_form url 1
CAAA001 3357 medication_arm_1 38416 cm_medication_use_log url 1
CAAA001 3357 v01_arm_1 48807 pi_participant_information url 1 
CABA001 3357 consent_arm_1 48831 cf_consent_form url 1 
CABA001 3357 eligibility_arm_1 48806 sc_screening_form url 1 
CABA001 3357 medication_arm_1 38416 cm_medication_use_log url 1 
CABA001 3357 v01_arm_1 48807 pi_participant_information url 1 
CACA001 3357 consent_arm_1 48831 cf_consent_form_documentation url 1
CACA001 3357 eligibility_arm_1 48806 sc_screening_form url 1 
CACA001 3357 medication_arm_1 38416 cm_medication_use_log url 1 
CACA001 3357 v01_arm_1 48807 pi_participant_information url 1 
CACA001 3357 v01_arm_1 48807 ba_baseline_demographics url 1
;

My master code would be: 

data master1 ;
  input id : $10. pidn unique_event_name :$20. event_id page :$30. 
  redcap_version :$20. instance ;
  cards ;
if id="CAAA001" and pidn=3357 and unique_event_name="consent_arm_1" and event_id=48831 and page="cf_consent_form" and redcap_version="url" and instance=1 then cats(redcap_version, pidn, id, page, event_id, instance);
else if id=CAAA001 and pidn=3357  and unique_event_name="eligibility_arm_1" and event_id=48806 and page="sc_screening_form" and redcap_version="url" and instance= 1 then cats (redcap_version, pidn, id, page, event_id, instance); 
else if id="CAAA001" and pidn=3357  and unique_event_name="medication_arm_1" and  event_id=38416 and page="cm_medication_use_log" and  redcap_version="url" and instance=1 then cats (redcap_version, pidn, id, page, event_id, instance); 
else if id="CAAA001" and pidn= 3357 and unique_event_name="v01_arm_1" event_id=48807 and page="pi_participant_information" and  redcap_version="url" and instance=1 hen cats (redcap_version, pidn, id, page, event_id, instance); 

and so on and so forth for all of the IDs in the master sheet. 

Would arrays and looping help to shorten this code? Could a macro help with reducing the code? 

@Patrick @Quentin @Tom @ballardw @PaigeMiller 

[Patrick]

Please provide the sample data via SAS datastep code with datalines the same way as I've done for creation of the have table.

I still "hear" that once you've got a set of valid rows the code as already provided is what you're after. So it's just about how to select these rows. 

It's certainly possible to write such selection logic also if it's not a simple merge. We just need to understand what you have and what you need. Sample data and desired outcome (as data - like add a flag column to your have data for the rows that should get selected) often helps tremendously to remove ambiguity. 

Try to only share the details that are relevant for the problem. I assume it's not important for resolving the problem at hand how the source data gets loaded.

[JackZ295]

---

@Patrick wrote:

Please provide the sample data via SAS datastep code with datalines the same way as I've done for creation of the have table.

I still "hear" that once you've got a set of valid rows the code as already provided is what you're after. So it's just about how to select these rows. 

It's certainly possible to write such selection logic also if it's not a simple merge. We just need to understand what you have and what you need. Sample data and desired outcome (as data - like add a flag column to your have data for the rows that should get selected) often helps tremendously to remove ambiguity. 

Try to only share the details that are relevant for the problem. I assume it's not important for resolving the problem at hand how the source data gets loaded.

---

Hi @Patrick , I edited my post with two new data sets, the initiation and the master data set, as well as the intended goal (without macros).

[Patrick]

@JackZ295 Generating all these IF/THEN/ELSE statements is highly likely a very sub-optimal design approach. But having said that I still don't understand your data and explanations because:

1. Your master table is already uploaded and surfaced via REDCap and the the initiation table also gets uploaded first so you've got all the data in the same place and already surfaced via REDCap

- if you would use the initial data to query the master table via REST calls then you wouldn't upload the data first

- where do you actually need the result to be written to in the end?

2. Your initiation table already contains all the information for matching rows that's also in the master table

- or are there initial fields in the master table that you need to retrieve and it's only the keys

- why can't you create the url's directly from the initiation table?

If in the end all of this code will be written in R then it's eventually better you get your R developer to design and implement this process from scratch instead of having to convert a SAS program. Plus your R developer already might have some background and more importantly you can talk directly to this person which will help clarifying things much quicker.

[JackZ295]

Hi @Patrick , as a point of clarification, the master table is NOT in REDCap; I am generating it in SAS. REDCap has their own data quality check system in place that users can utilize to place in their own checks/queries. However, our project is so large that we've decided to do those checks outside of REDCap and simply use a database to keep a record of them. 

1. For your first point, what do you mean by REST calls? 

2. The resulting data would be a new column in the initiation table which would contain the links for each affected ID. The initiation table doesn't currently have the links, as these are generated by R/SAS code. 

3. We can't create the URLs from the initiation table because they're not in the initiation table. There is no place in REDCap where this is present; we are piecing together the elements of the URL based off of what we know is contained in a REDCap URL. Our Data Quality Query database has a field for the link to be uploaded. We want each query in our Data Quality Query database to have a link to the record of interest so that sites participating in the study can directly go to the record to address an issue if an issue is flagged for a particular record (e.g. if it's a data entry issue, etc.). 

---

@Patrick wrote:

@JackZ295 Generating all these IF/THEN/ELSE statements is highly likely a very sub-optimal design approach. But having said that I still don't understand your data and explanations because:

1. Your master table is already uploaded and surfaced via REDCap and the the initiation table also gets uploaded first so you've got all the data in the same place and already surfaced via REDCap

- if you would use the initial data to query the master table via REST calls then you wouldn't upload the data first

- where do you actually need the result to be written to in the end?

2. Your initiation table already contains all the information for matching rows that's also in the master table

- or are there initial fields in the master table that you need to retrieve and it's only the keys

- why can't you create the url's directly from the initiation table?

If in the end all of this code will be written in R then it's eventually better you get your R developer to design and implement this process from scratch instead of having to convert a SAS program. Plus your R developer already might have some background and more importantly you can talk directly to this person which will help clarifying things much quicker.

---

[Quentin]

Please try and let go of the idea that you need multiple IF statements (one per record).

You have this data (thanks for posting):

data initiation ;
  input id : $10. pidn unique_event_name :$20. event_id page :$30. redcap_version :$20. instance ;
  cards ;
CAAA001 3357 consent_arm_1 48831 cf_consent_form  url 1
CAAA001 3357 eligibility_arm_1 48806 sc_screening_form url 1
CAAA001 3357 medication_arm_1 38416 cm_medication_use_log url 1
CABA001 3357 v01_arm_1 48807 pi_participant_information url 1 
CACA001 3357 v01_arm_1 48807 ba_baseline_demographics url 1 
;

Your goal is to create a new variable QI106, correct?  And for this data with 5 records, you would want 5 different values for QI106.  The easiest way to create QI106 is to calculate it in a simple DATA step:

data want ; 
  set initiation; 
  length QI106 $200;
  qi106=cats(redcap_version, "DataEntry/index.php?pid=", pidn, '&id=', id, '&page=', page, '&event_id=', event_id, '&instance=', instance);
run;

proc print ;
run ;

That will happily calculate QI106, whether you have 5 rows of data in INITIATION or 5,000,000.  

Please take a look at the output from that PROC PRINT, and explain why the result is not what you want.

I don't see the need for a lookup table (master) here.  And definitely don't see the need for having multiple IF statements, an array, or a macro.

[Quentin]

@JackZ295 were you able to resolve your problem? You got a lot of ideas in this thread.  Is there one you can mark as an accepted answer?  Or could you add an answer if you found an alternate solution?

[Tom]

Why do you have the IF statements if for everyone you end up running the exact same code to generate the URL string?

[JackZ295]

Hi @Quentin and @PaigeMiller , thanks for your help again. @Quent17 's suggested code works: 

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;

Here is my code and log: 

/*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: 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 1.49 seconds
cpu time 0.95 seconds

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

And here is the sample data (non-Excel spreadsheet):