topic Re: Error message during concatenation: Variable already exists on file in SAS Programming

Error message during concatenation: Variable already exists on file

FoxMulder — Fri, 10 Nov 2017 04:57:09 GMT

Hi all,

I have been receiving this error message during the concatenation of two files which have a variable with different data types: "Variable Level already exists on file WORK.ALL_PAYS". The outcome of the concatenation is partially OK, since there are some observations (the two ones from the file Pay 1996, variable 'Level') that should have data, but they are actually with missing values.

Below is my code:

Data Pay1995;
Input ID $ 1-3 Level $ 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A23 32 68000 M
A24 35   7500 F
A30 44 97000 M
A13 28 27000 F
;
run;

Data Pay1996;
Input ID $ 1-3 Level $ 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A25 29 35000 F
A26 36 88000 F
;
run;

*To concatenate the two datasets successfully, you need to change the Level variable in one of the data sets so the types match.
Use either the PUT or INPUT function to modify your variable type.;

Data work.pay1995;
     set work.pay1995;
     Level2=input(Level,4.);
run;

*Use appropriate DROP and RENAME statements or options to achieve this in ONE DATA step.;

Data work.All_Pays (RENAME=(LEVEL2=Level));
         set   work.Pay1995 (drop =Level) work.Pay1996;
run;

Please let me know how can I improve my code and fix the error(s).

Regards

Re: Error message during concatenation: Variable already exists on file

PGStats — Fri, 10 Nov 2017 05:09:01 GMT

I guess you meant :

Input ID $ 1-3 Level 5-7 Salary 9-14 Gender $ 16-17;

(without the $) for one of your example datasets.

Re: Error message during concatenation: Variable already exists on file

FoxMulder — Fri, 10 Nov 2017 06:19:29 GMT

That's the point: one of the original variables (level), is defined as 'Character' in one data set and 'Numeric' in the other data set. That's why I introduced the $ . I did that on purpose to expose the issue described above.

Thanks!

Re: Error message during concatenation: Variable already exists on file

FoxMulder — Fri, 10 Nov 2017 06:22:22 GMT

Yes, you right..I have seen you point .I made a mistake...Let me copy the code again:

data Pay1995;
Input ID $ 1-3 Level 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A23 32 68000 M
A24 35   7500 F
A30 44 97000 M
A13 28 27000 F
;
run;

data Pay1996;
Input ID $ 1-3 Level $ 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A25 29 35000 F
A26 36 88000 F
;
run;

*To concatenate the two datasets successfully, you need to change the Level variable in one of the data sets so the types match.
Use either the PUT or INPUT function to modify your variable type.;

data work.pay1995;
     set work.pay1995;
     Level2=input(Level,4.);
run;

*Use appropriate DROP and RENAME statements or options to achieve this in ONE DATA step.;

data work.All_Pays (RENAME=(LEVEL2=Level));
         set   work.Pay1995 (drop =Level) work.Pay1996;
run;

Sorry for the confusion.

Thanks!

Re: Error message during concatenation: Variable already exists on file

FoxMulder — Fri, 10 Nov 2017 06:25:53 GMT

I have changed the code to this:

data Pay1995;
Input ID $ 1-3 Level $ 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A23 32 68000 M
A24 35   7500 F
A30 44 97000 M
A13 28 27000 F
;
run;

data Pay1996;
Input ID $ 1-3 Level 5-7 Salary 9-14 Gender $ 16-17;
Datalines;
A25 29 35000 F
A26 36 88000 F
;
run;

*To concatenate the two datasets successfully, you need to change the Level variable in one of the data sets so the types match.
Use either the PUT or INPUT function to modify your variable type.;

data work.pay1995;
     set work.pay1995;
     Level2=input(Level,4.);
run;

*Use appropriate DROP and RENAME statements or options to achieve this in ONE DATA step.;

data work.All_Pays (RENAME=(LEVEL2=Level));
         set   work.Pay1995 (drop =Level) work.Pay1996;
run;

Re: Error message during concatenation: Variable already exists on file

PGStats — Fri, 10 Nov 2017 06:29:36 GMT

I would concatenate this way


data all;
set 
    pay1995 
    pay1996 (in=in2 rename=level=levelStr);
if in2 then level = input(levelStr, best.);
drop levelStr;
run;

Re: Error message during concatenation: Variable already exists on file

FoxMulder — Thu, 23 Nov 2017 05:40:10 GMT

Thank you, your solution solved the problem!

Sorry for my late reply