How do I join fields that exist in new datasets, but not in previous datasets?

[_SASEG_]

Hello,

I am using SAS EG version 7.1.

I am trying to build a bit of code where it can be used for the last 6 years. I am selecting the same fields, no matter the month. Unfortunately my piece of code has an error, due to the field not being in previous datasets where the field was added into the file. The 2 errors are as follows:

ERROR: Column Cue5 could not be found in the table/view identified with the correlation name B.

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

PROC SQL;
	CREATE TABLE Data AS
	SELECT a.*
	,b.Cue1
	,b.Cue2
	,b.Cue3 AS Cue3_Old
	,b.Cue4 AS Cue4_Old	
    ,(CASE WHEN Version < '4' THEN .
		   WHEN Version >= '4' THEN b.Cue5
		   ELSE . END) AS Cue5_Old
	FROM File AS a
	LEFT JOIN extra_data AS b ON a.id = b.id;
QUIT;

On the File that I am joining on the extra_data to, Cue5 doesn't exist before version 4, so I thought in the case when statement, it would put a missing value. It seems that it is failing due to Cue5 not existing in the dataset extra_data. All fields are numeric. The code works perfect for version 4 and above.

Does anyone know how to fix it? Any help would be greatly appreciated!

Thank you!

[Reeza]

Can you use a data step instead? I'm not aware of a method to do this via SQL but via a data step it can be done.

[_SASEG_]

Thanks for responding.

I'm not sure of a way to do it in a data step, as I'm doing a left join from a table that has millions of records. Do you know how I would be able to select everything from table a and only add a few fields on from table b?

Thanks!

[Kurt_Bremser]

You need to determine the version before the step, and do the cue5 code conditionally with %IF statements.

proc sql;
create table data as
  select
  a.*
  ,b.Cue1
  ,b.Cue2
  ,b.Cue3 AS Cue3_Old
  ,b.Cue4 AS Cue4_Old
%if &version ge 4 %then %do;
  ,b.Cue5 as Cue5_old
%end;
%if &version le 3 %then %do;
  ,. as Cue5_old
%end;
  from file as a
  left join extra_data as b on a.id = b.id
;
quit;

[_SASEG_]

Thanks Kurt.

Do you mean set version as a macro? If so, version is not set by me, but is just listed on the dataset 'file'. There could be several different version numbers, per different id, so I am trying to keep it easy for whoever will run the code in future.

Is it possible to automate the macro without any manual intervention?

[Reeza]

data want;
*declares the variable - now on all data sets;
*ensure type and length is correct;
length CUE5 $8. CUE5_OLD $10;

merge file (in=t1) extra_data (in=t2);
by ID;
if t1;

*handle rename/keep statements here;
run;

Use a data step and the IN option to replicate the LEFT JOIN from SQL. 

Then declare the variable at the top of the data step with a length statement which ensures its in all data sets. This will not affect data sets if the variable already exists and you have it set to the same length/type. So then your code should work across the different version types. The code above should get you started to test it out. 

---

@_SASEG_ wrote:

Hello,

I am using SAS EG version 7.1.

I am trying to build a bit of code where it can be used for the last 6 years. I am selecting the same fields, no matter the month. Unfortunately my piece of code has an error, due to the field not being in previous datasets where the field was added into the file. The 2 errors are as follows:

ERROR: Column Cue5 could not be found in the table/view identified with the correlation name B.

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

PROC SQL;
	CREATE TABLE Data AS
	SELECT a.*
	,b.Cue1
	,b.Cue2
	,b.Cue3 AS Cue3_Old
	,b.Cue4 AS Cue4_Old	
    ,(CASE WHEN Version < '4' THEN .
		   WHEN Version >= '4' THEN b.Cue5
		   ELSE . END) AS Cue5_Old
	FROM File AS a
	LEFT JOIN extra_data AS b ON a.id = b.id;
QUIT;

On the File that I am joining on the extra_data to, Cue5 doesn't exist before version 4, so I thought in the case when statement, it would put a missing value. It seems that it is failing due to Cue5 not existing in the dataset extra_data. All fields are numeric. The code works perfect for version 4 and above.

Does anyone know how to fix it? Any help would be greatly appreciated!

Thank you!

---

[Tom · Accepted solution]

Your code is assuming the VERSION has the same value for every observation in EXTRA_DATA.

In that case just test the first observation and use that to set a macro variable with either the variable name or the default value.  It looks like you are expecting CUE5 to be numeric so use a missing numeric value (a period).

PROC SQL;
select case when (version <'4') then '.' else 'B.CUE5' end
  into :cue5 trimmed 
  from extra_data 
;

CREATE TABLE Data AS
  SELECT a.*
  ,b.Cue1
  ,b.Cue2
  ,b.Cue3 AS Cue3_Old
  ,b.Cue4 AS Cue4_Old	
  , &cue5  AS Cue5_Old
  FROM File AS a
  LEFT JOIN extra_data AS b
    ON a.id = b.id
;
QUIT;

But is is much easier to do this type of combination with SAS code instead of SQL code.

You can use KEEP= and RENAME= dataset options to pick which "extra" variables to take.  You can even suppress the error/warning if the variable does not exist.

%let saveopt = %sysfunc(getoption(dkricond,keyword));
options dkricond=nowarn;
data DATA ;
  merge FILE(in=in1)
        EXTRA_DATA(keep=id cue1-cue5 
            rename=(cue3=cue3_old cue4=cue4_old cue5=cue5_old))
  ;
  by id;
  if in1;
run;
options &saveopt;

[_SASEG_]

Thanks for that Tom.

I tried the data step and it worked, without an error. For the case when I used a file with a version before 4, the field wasn't created, but it didn't error. I combined it with your suggestion for the proc sql and used an IF statement, and it populated a missing value.

Thanks so much for helping me! @Tom 

Thanks again for everyone else for your help too @Reeza @Kurt_Bremser