Merging multiple datasets using macro, without having same varialbes in them

[sasuser_221]

%let dataset_list = member_1 member_2 member_3 member_4 member_5;

%let var_list = member_name coverage_st_dt coverage_end_dt policy_ext;




%macro file_process;
%let word_cnt = %sysfunc(countW(&dataset_list));
%do i = 1 %to &word_cnt;

data dataset_1;

set datamart.%qscan(%bquote(&dataset_list),&i)(keep= &var_list );
proc append base= dataset_2 data= dataset_1;
run;
%end;


%mend file_process;

%file_process;

I'm working on merging mutliple datasets into one dataset using a single macro function. I'm passing the required datasets and variables names using a macro variable. this is working fine, but in couple of datasets i don't have all the variables that I'm passing.  code is failing here. below is the code i developed so far.

My requirement is to merge the datasets, eventhough all the datasets doesn't have same variables.

[Kurt_Bremser · Accepted solution]

%macro file_process;

data dataset_2;
set
%do i = 1 %to %sysfunc(countw(&dataset_list.));
  datamart.%scan(&dataset_list.,&i.)
%end;
;
keep &var_list.;
run;

%mend;

[sasuser_221]

Thank you for your reply and its working now.

I'm getting results as expected, but with warning sometimes.

Is there any other possible way to get rid of the warnings.

[Kurt_Bremser]

---

@sasuser_221 wrote:

Thank you for your reply and its working now.

I'm getting results as expected, but with warning sometimes.

Is there any other possible way to get rid of the warnings.

---

When the data step creates WARNINGs, it is because you have variables in later datasets with a longer defined length than they had in the first dataset where they were encountered, as this might lead to truncation of data.

You have two options:

• find the maximum defined length of all variables in all your datasets; can be retrieved from dictionary.columns. Use this to create a length statement that you place before the set.
• make sure you have a reliable data import process. Stop using proc import, read your external data with data steps where you take complete control over column attributes, according to the specifications you got along with the data. Then a sepcific variable will be defined with a consistent length across all your datasets.

The second option is to be preferred by orders of magnitude. Really. I mean it.

[sasuser_221]

I'm getting below warning

WARNING: The variable var1 in the DROP, KEEP, or RENAME list has never been referenced.

how to overcome this type of warning?

[Reeza]

Check your DROP, KEEP/RENAME statements to see if any reference a variable that no longer exists for some reason. 

You can comment out each one and run it and see which generates the error and then fix that one. 

---

@sasuser_221 wrote:

I'm getting below warning

WARNING: The variable var1 in the DROP, KEEP, or RENAME list has never been referenced.

how to overcome this type of warning?

---

[sasuser_221]

i'm using a loop to iterate through multiple datasets and a macro variable with all the variable that I need from the datasets.

the catch is not all datasets have all the variables that I'm pulling, that is why I'm getting a warning.

[Reeza]

If not all datasets have the variables, then you have the warning correctly. You can either turn off the warning or live with it if you don't want to change the code to adapt for the variables that may not exist.

[Tom]

---

@sasuser_221 wrote:

I'm getting below warning

WARNING: The variable var1 in the DROP, KEEP, or RENAME list has never been referenced.

how to overcome this type of warning?

---

Set the DKRICOND option to NOWARN.

https://documentation.sas.com/?docsetId=lesysoptsref&docsetTarget=p11ksgdhjx7369n1dpiry84z7gjn.htm&d...

[Kurt_Bremser]

---

@sasuser_221 wrote:

I'm getting below warning

WARNING: The variable var1 in the DROP, KEEP, or RENAME list has never been referenced.

how to overcome this type of warning?

---

The code I gave you (and which you accepted as solution) CAN NOT cause this WARNING, as it does not have a drop, keep or rename.

[Reeza]

Any reason not to use the shortcut operator?

data want;
set datamart.member_1 - datamart.member_5;

keep &var_list;
run;

PROC APPEND cannot add new variables. In general, I would recommend creating a master data set that has the types and all the variables you want and then append into that data set. I usually use PROC SQL to create those empty tables, though if you have a table that is like that you can make the skeleton table easily using obs=0 option. Then use the skeleton table in your PROC APPEND as your base. I'm just labeling it skeleton for illustration purposes you can call it whatever  you'd like.

*make skeleton;

data skeleton;
set tableProperties (obs=0);
run;

[Tom]

Perhaps you just need to change the setting for the DKRICOND option?

Why not have your macro generate code like this?

options dkricond=nowarn;
data want ;
  set member_1(keep=&var_list)
      member_2(keep=&var_list)
   ...
  ;
run;

[ballardw]

---

@sasuser_221 wrote:

My requirement is to merge the datasets, eventhough all the datasets doesn't have same variables.

---

In general SAS terms 'MERGE' means a side-by-side combination, usually on a common value(s).

data like:

Name age

John   16

Name  grade

John     A

and a merge results in

Name age grade

John   16   A

Since you mention append that would be a vertical stacking or use of SET statements in a data step.

[Ksharp]

Try add an option:

proc append base= dataset_2 data= dataset_1   force  ;
run;