Proc Append Duplicates

[Toni2]

hi i have a macro loop to append data which returns me duplicates for some reason for vars 2, 3 and 4

I want to return unique values and not duplicates

Please note i want this to apply to a wider macro and this is an example (there are more variables)

What i get from this macro is this :

Index	Variable
0.008232	var1
0.003461	var2
0.003461	var2
0.006708	var3
0.006708	var3
0.000037	var4
0.000037	var4

%macro test1;
%do i=1 %to 4;


proc append base=data1 data=data_&i force;
run;

data data1;
   set 
%if &i > 1 %then data1 ; data_&i;
run;


%end;
%mend;

proc delete data=data1;
run;

%test1;

[PaigeMiller]

First you do PROC APPEND, then you do SET, that's why you get duplicates. Essentially APPEND and SET do the same thing in this example.

As I said in your other thread, macros are not needed here.

data want;
    set var1 var2 var3 ... ;
run;

Since you have to type the list of data sets for the macro to work, just type it into the SET statement.

[Toni2]

thanks for having look at this. It seems that i made a mistake in the code - apologies. I have now amended my initial post. Could you please have a quick look?

[PaigeMiller]

I don't see a difference. You still have made this unnecessarily complicated. Macros are not needed. PROC APPEND followed by a SET in a data step is redundant.

[Toni2]

thanks and i am sorry as i am new here. How can i adjust the data step in order to merge all the data sets without typing all the names of the datasets?

[PaigeMiller]

Explain how this list of data set names is known. Somehow, the programmer has to know how to access the list of names. Is it all the SAS data sets in a certain folder? 

[Toni2]

This is part of a macro loop

I get this list from this part of the code (see in the bottom). The value of &i takes values from 1 to value that let's say is equal to the number of variables.

All files are saved in the WORK library 

i want in the last loop to merge (if it is correct) all these dataset into one

data modeling_final;
set modeling_oot_1 data modeling_oot_2 data modeling_oot_3.......data modeling_oot_last;
run;

data modeling_oot_&i; 
set modeling_oot_freq for_total;
keep order_rank PSI;
run;

[PaigeMiller]

---

@Toni2 wrote:

This is part of a macro loop

---

Then how does the programmer know what the file names are, so these names can be fed into the macro? I assume that it will change if you run the code today or if you run the code next month, otherwise there is no need for a macro.

I'm not looking for example code now ... I am looking for a higher level explanation of the problem.

[Toni2]

the code is huge in order to copy/paste here. In the bottom you can see the beginning of this code  (it is not all the code. it is only a part)

This macro is where (i believe) the &i is defined based on the code that i have copied below

%macro meannum;

%do i=1 %to &vmcnt;

/* list of numeric variables */
%let binnum=10; /* number of bins for numeric variables */
%let vartxt=ACCOUNT_STATUS; /* list of character variables */
%let imtxt=____; /* label for missing character values */
%let missnum=-999999999; /* label for missing character values */
%let yaxislabel=% Frequency; /* label for distribution */
%let labelmod=Population; /* label for modeling sample */
%let labeloot=Sample; /* label for validation sample */


********* Part II: Numeric Variables *********;

%macro dealnum;

%if %sysfunc(countw(&varnum dummyfill)) > 0 %then %do;

data check_contents;
retain &varnum;
set &inputset(keep=&varnum obs=1);
run;

proc contents data=check_contents varnum out=check_contents2 noprint;
run;

proc sort data=check_contents2(keep=name varnum) out=checkfreq(rename=(name=tablevar)); 
by varnum; 
run;

data varcnt; 
set checkfreq; 
varcnt+1;
run;

proc sql noprint;
select tablevar into :varmore separated by ' ' from varcnt;
quit;

proc sql;
create table vcnt as select count(*) as vcnt from varcnt;
quit;

data _null_; 
set vcnt;
call symputx('vmcnt', vcnt);
run;

proc sql noprint; 
select tablevar into :v1-:v&vmcnt from varcnt;
quit;

proc rank data=&inputset group=&binnum out=check_rank ties=low;
var &varnum;
ranks rank1-rank&vmcnt; 
run;

data check_rank;
set check_rank;
array fillmiss(*) rank1-rank&vmcnt;
do j=1 to dim(fillmiss);
if fillmiss(j)=. then fillmiss(j)=-1;
fillmiss(j)=fillmiss(j)+1;
end;
drop j;
run;

%macro meannum;
%do i=1 %to &vmcnt;

[PaigeMiller]

I'm not looking for example code now ... I am looking for a higher level explanation of the problem.

[Toni2]

thanks for the support

[Toni2]

thank you very much for the help 🙂

[andreas_lds]

Imho you should use proc append OR the data step.

[Tom]

You are getting duplicates because you are appending the new data twice. 

Once using PROC APPEND and once using a DATA step.

Just pick one ONE of those two methods to use in your %DO loop.

[Toni2]

thanks but my problem is the code for some reason creates duplicates