How to merge data sets that are optional (may be missing) Using SAS 9.2 (TS2M3)

[tanya_henderson]

I am trying to merge data sets that may or may not exist. 

data visits;
set sc_visit bl_visit v01_visit v02_visit pw;
run;

If the subjects have not reached the V02 visit then v02_visit data set will not exist. 

How do I do this combination of data sets if a data set may or may not exist?

More complete documentation is attached.  

This odd combination is because I am pulling data sets from REDCap which provides one record per event. 

ERROR MESSAGE:

1672 data visits;
1673 set sc_visit bl_visit v01_visit v02_visit pw;
ERROR: Variable coord has been defined as both character and numeric.
1674 run;

[VDD]

ERROR: Variable coord has been defined as both character and numeric.

define coord as character or numeric in all datasets containing coord

[tanya_henderson]

coord is numeric in all data sets. The issue becomes character if the data set doesn't exist.

[Reeza]

Did you read these in from Excel, a CSV, or directly from RedCap? Single record for event, is common practice.

[tanya_henderson]

I am reading these in from a CSV. The main csv files are the health and screen data sets. 

[Reeza]

Guessing you used PROC IMPORT? I think you're going to need to go back, read them all in with data steps and ensure your types are read in correctly. PROC IMPORT makes guesses at types and sometimes it's wrong, which is why you get what's happening above.

You could try add GUESSINGROWS=MAX to your PROC IMPORT but that's not a guarantee by any means.

[tanya_henderson]

The issue is that some events have not yet occurred. There are no V01 visits that meet the criteria. So, when I try to merge in the V01_visit data set to the final results, it causes errors.

data visits;
set sc_visit bl_visit /*v01_visit v02_visit pw*/;
run;

If I comment out the data sets as above, the report works fine. How do I account for data that may not exist?

I am looking for something like this:

data visits;
set sc_visit bl_visit  %if (exists(v01_visit))  %if (exists(v02_visit)) %if (exists(pw));
run;

[Reeza]

 Is there a pattern to the naming structure of the files you'd like to combine that allows you to uniquely identify those data sets?

If it catches the 0 ones that's fine. If there is, you can dynamically generate the list of names. Because you're on such an old version I don't think you can use shortcut lists. SAS 9.2 is really, really old by the way, if you have any options to push for an upgrade do so.

If you're in Academia, SAS UE is freely available.

---

@tanya_henderson wrote:

The issue is that some events have not yet occurred. There are no V01 visits that meet the criteria. So, when I try to merge in the V01_visit data set to the final results, it causes errors.

data visits;
set sc_visit bl_visit /*v01_visit v02_visit pw*/;
run;

If I comment out the data sets as above, the report works fine. How do I account for data that may not exist?

I am looking for something like this:

data visits;
set sc_visit bl_visit  %if (exists(v01_visit))  %if (exists(v02_visit)) %if (exists(pw));
run;

---

[Patrick · Accepted solution]

@tanya_henderson 

You could query the dictionary tables and populate a macro variable with data sets that actually exist. You then use this macro variable in your set statement.

data sc_visit v02_Visit;
  stop;
run;

%let ds_list=;
proc sql;
  select memname into :ds_list separated by ' '
  from dictionary.tables
  where 
    libname='WORK'
    and memname in ('SC_VISIT', 'BL_VISIT', 'V01_VISIT', 'V02_VISIT', 'PW')
  ;
quit;
%put &=ds_list;

data want;
  set &ds_list;
run;

[ballardw]

---

@tanya_henderson wrote:

I am trying to merge data sets that may or may not exist. 

data visits;
set sc_visit bl_visit v01_visit v02_visit pw;
run;

If the subjects have not reached the V02 visit then v02_visit data set will not exist. 

How do I do this combination of data sets if a data set may or may not exist?

More complete documentation is attached.  

This odd combination is because I am pulling data sets from REDCap which provides one record per event. 

ERROR MESSAGE:

1672 data visits;
1673 set sc_visit bl_visit v01_visit v02_visit pw;
ERROR: Variable coord has been defined as both character and numeric.
1674 run;

---

If the actual concern is getting files that exist to set together when you don't know which ones actually exist you have two basic approaches: 1) something that tests the existence of the data before placing the name on the set statement which might look something like

data _null_;
   length namestr $ 100;
   if exist('work.sc_visit') then namestr= 'work.sc_visit';
   if exist('work.bl_visit') then namestr = catx(' ',namestr,'work.bl_visit');
   if exist('work.v02_visit') then namestr = catx(' ',namestr,'work.v02_visit');
   if exist('work.pw') then namestr = catx(' ',namestr,'work.pw');
   call execute ('data visits; set');
   call execute (namestr);
   call execute (' ; run;');
run;

Or similar approach with macros.

If the question is your data incompatibility you need to go back to your data read steps which appear to have been removed from the code given the comments and header:

/*
## PROGRAM NAME: missing_assess.sas
##       AUTHOR: Tanya Henderson
##         DATE: November 2018
##  DESCRIPTION: import csv export from redcap
##
*/


proc sql; 
create table sc_visit as select h.guid,'SC' as visit , h._compltyn , h._visstat_com, s._coord as coord
from health h left join screen s on h.guid = s._guid and h._redcap_event_name = s._redcap_event_name  and h._compltyn = 0 and h._redcap_event_name = 'screening_arm_1' order by guid; 

create table bl_visit as select h.guid,'BL' as visit , h._compltyn , h._visstat_com, s._bl_coord as coord
from health h left join health s on h.guid = s.guid and h._compltyn = 0 and h._redcap_event_name = 'baseline_arm_1' and s._redcap_event_name ='baseline_pre_visit_arm_1' order by guid; 

create table v01_visit as select h.guid,'V01' as visit , h._compltyn , h._visstat_com, s._v01_coord as coord
from health h left join health s on h.guid = s.guid and h._compltyn = 0 and h._redcap_event_name = 'month_12_arm_1' and s._redcap_event_name ='month_12_pre_visit_arm_1' order by guid; 

create table v02_visit as select h.guid,'V02' as visit , h._compltyn , h._visstat_com, s._v02_coord as coord
from health h left join health s on h.guid = s.guid and h._compltyn = 0 and h._redcap_event_name = 'month_24_arm_1' and s._redcap_event_name ='month_24_pre_visit_arm_1' order by guid; 

create table pw as select guid,'PW' as visit , _compltyn , _visstat_com, 0 as coord
from health where _redcap_event_name = 'premature_withdraw_arm_1' and _compltyn = 0 order by guid; 
quit; 

Your comment says "import csv" but there is absolutely no code that does such. One suspects Proc Import which makes separate guesses for each file read.