topic Re: Merging multiple datasets using macro, without having same varialbes in them in New SAS User

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

sasuser_221 — Thu, 19 Sep 2019 15:41:30 GMT

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

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

Kurt_Bremser — Thu, 19 Sep 2019 15:57:30 GMT

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

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

Reeza — Thu, 19 Sep 2019 16:06:43 GMT

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;

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

Tom — Thu, 19 Sep 2019 16:16:29 GMT

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;

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

ballardw — Thu, 19 Sep 2019 16:59:57 GMT

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

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

Ksharp — Fri, 20 Sep 2019 12:46:04 GMT

Try add an option:

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

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

sasuser_221 — Wed, 06 Nov 2019 22:01:21 GMT

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.

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

Kurt_Bremser — Thu, 07 Nov 2019 06:40:46 GMT

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

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

sasuser_221 — Mon, 11 Nov 2019 22:27:47 GMT

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?

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

Reeza — Mon, 11 Nov 2019 22:39:01 GMT

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?

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

sasuser_221 — Mon, 11 Nov 2019 22:43:28 GMT

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.

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

Reeza — Mon, 11 Nov 2019 22:49:39 GMT

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.

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

Tom — Mon, 11 Nov 2019 22:59:17 GMT

@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&docsetVersion=9.4&locale=en

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

Kurt_Bremser — Tue, 12 Nov 2019 12:00:31 GMT

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