%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.
%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;
%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;
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.
@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:
The second option is to be preferred by orders of magnitude. Really. I mean it.
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?
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?
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.
@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.
@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.
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;
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;
@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.
Try add an option:
proc append base= dataset_2 data= dataset_1 force ;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.