topic Re: Proc Append Duplicates in SAS Programming

Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 10:03:15 GMT

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;

Re: Proc Append Duplicates

PaigeMiller — Thu, 28 Apr 2022 09:54:20 GMT

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.

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 10:04:35 GMT

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?

Re: Proc Append Duplicates

PaigeMiller — Thu, 28 Apr 2022 10:16:39 GMT

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.

Re: Proc Append Duplicates

andreas_lds — Thu, 28 Apr 2022 10:43:15 GMT

Imho you should use proc append OR the data step.

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 11:02:18 GMT

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?

Re: Proc Append Duplicates

PaigeMiller — Thu, 28 Apr 2022 11:21:33 GMT

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?

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 11:56:17 GMT

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;

Re: Proc Append Duplicates

PaigeMiller — Thu, 28 Apr 2022 12:03:15 GMT

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

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 12:18:54 GMT

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;

Re: Proc Append Duplicates

PaigeMiller — Thu, 28 Apr 2022 12:21:29 GMT

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

Re: Proc Append Duplicates

Tom — Thu, 28 Apr 2022 13:36:53 GMT

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.

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 14:31:27 GMT

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

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 14:31:46 GMT

thanks for the support

Re: Proc Append Duplicates

Tom — Thu, 28 Apr 2022 14:41:25 GMT

@Toni2 wrote:
thanks but my problem is the code for some reason creates duplicates

Remove either the PROC APPEND step or the DATA step and it will not duplicate the data.

Just to spell it out:

You want to either use code like this:

%do i=1 %to 4;
proc append base=data1 data=data_&i force;
run;
%end;

OR code like this

%do i=1 %to 4;
data data1;
   set 
%if &i > 1 %then data1 ; data_&i;
run;
%end;

But don't use both step because then DATA_&I is appended TWICE.

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 15:10:32 GMT

i use the PROC Append and works, thanks and sorry for the confusion.
Actually, now what happens is, when i run the code multiple times then the data1 becomes bigger and bigger...any idea how to resolve this? (sorry possibly this is what i missed to explain)

Actually, this is what i can see

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

Re: Proc Append Duplicates

Tom — Thu, 28 Apr 2022 15:10:43 GMT

I assume that you just need to REMOVE the old dataset before running the program again.

proc delete data=data1; run;
%do i=1 %to 4;
proc append base=data1 data=data_&i force;
run;
%end;

Re: Proc Append Duplicates

Toni2 — Thu, 28 Apr 2022 15:28:39 GMT

it works again! thanks! When i run this for first time i get a warning that the data1 does not exist. Is there any way to avoid this as well? i ask since this is going to be part of a huge code and i want to avoid warnings/errors

Re: Proc Append Duplicates

Tom — Thu, 28 Apr 2022 15:49:20 GMT

Run it conditionally.

%if %sysfunc(exist(data1)) %then %do;
proc delete data=data1; run;
%end;

Use PROC DATASETS instead.

proc datasets nolist nowarn lib=work;
  delete data1;
run;
quit;

Re: Proc Append Duplicates

Toni2 — Fri, 29 Apr 2022 10:44:33 GMT

Thanks for the support 🙂