Error message during concatenation: Variable already exists on file

[FoxMulder]

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

[PGStats]

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.

[FoxMulder]

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!

[FoxMulder]

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!

[FoxMulder]

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;

[PGStats · Accepted solution]

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;

[FoxMulder]

Thank you, your solution solved the problem!

Sorry for my late reply