topic Re: Merge Datasets With a Conditional Drop Statement in SAS Programming

Merge Datasets With a Conditional Drop Statement

madtex99 — Tue, 24 Oct 2023 22:57:11 GMT

data work.combined;
	merge TMP1.HW10Demo (in=inD) TMP2.HW10Baseline (in=inB);
	by Site;
	if inD and inB then Complete=11;
	else if inD and ~inB then Complete=10;
	else Complete=01;
	Age=yrdif(DOB,Entry,'act/act');
	TTime=datdif(Entry,Test,'act/act');
data work.DBF;
	merge work.combined TMP3.HW10Followup;
	FupTime=datdif(Entry,fupdt,'act/act');
data work.SID;
	merge TMP1.HW10Baseline (in=inB) TMP3.HW10Followup (in=inF);
	by Site;
	if inB and inF then keep Site ID fupdt fuptest Test Score;
	else if inB and ~inF then drop fupdt fuptest Test Score;
run;

My last data step I am trying to merge those two sets and only keep variables that have both Baseline and Follow Up data in the new set. I am getting an error that says my variables have never been referenced. Help please!

Re: Merge Datasets With a Conditional Drop Statement

Astounding — Tue, 24 Oct 2023 23:05:35 GMT

You wil need to re-think what you are trying to do here.

First, in a SAS data set, all variables exist on every observation. There is no possibility of keeping (or dropping) variables differently from one observation to the next.

Second, KEEP or DROP (no matter how and where specified) cannot be executed conditionally. They cannnot be part of an IF THEN statement.

Theoretically possible if it meets your needs: You can create two output data sets. You could keep a different set of variables in each output data set. And you might use the in= variables to determine which observations get output to which data sets.

Re: Merge Datasets With a Conditional Drop Statement

Patrick — Wed, 25 Oct 2023 01:50:54 GMT

To add to what @Astounding wrote.

SAS tables are rectangular and each observation (row) will have the same set of variables (columns).

SAS variables (the program data vector) get defined during the compilation phase of a data step which is another reason why you can't drop them conditionally during the execution phase.

Overview of the DATA Step

To understand the distinction between compilation and execution phase is important and will help you with defining program logic.

Statements like length, attrib, array, retain etc. are all on compilation level and though can't get used conditionally during the execution phase.

Think of the compilation phase as an iteration zero where the SAS compiler scans through your code and prepares everything for execution.

Re: Merge Datasets With a Conditional Drop Statement

Tom — Wed, 25 Oct 2023 02:11:48 GMT

You should get in the habit of including RUN (or QUIT where appropriate) to help the humans that have to read the code.

data combined;
  merge TMP1.HW10Demo (in=inD) TMP2.HW10Baseline (in=inB);
  by Site;
  if inD and inB then Complete=11;
  else if inD and ~inB then Complete=10;
  else Complete=01;
  Age=yrdif(DOB,Entry,'act/act');
  TTime=datdif(Entry,Test,'act/act');
run;

data DBF;
  merge combined TMP3.HW10Followup;
  FupTime=datdif(Entry,fupdt,'act/act');
run;

You could write THREE datasets in the last data step. That is the only way to conditionally change the number of variables.

data SID1(keep=Site ID fupdt fuptest Test Score) 
     SID2(drop=fupdt fuptest Test Score)
     SID3
;
  merge TMP1.HW10Baseline (in=inB) TMP3.HW10Followup (in=inF);
  by Site;
  if inB and inF then output sid1 ; 
  else if inB and ~inF then output sid2; 
  else output sid3;
run;