DATA Step, Macro, Functions and more

Error message during concatenation: Variable already exists on file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Error message during concatenation: Variable already exists on file

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


Accepted Solutions
Solution
‎11-23-2017 12:39 AM
Esteemed Advisor
Posts: 5,399

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

Posted in reply to FoxMulder

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;
PG

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,399

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

Posted in reply to FoxMulder

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.

PG
Occasional Contributor
Posts: 6

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

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!

Occasional Contributor
Posts: 6

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

Posted in reply to 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!

Occasional Contributor
Posts: 6

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

Posted in reply to 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;

Solution
‎11-23-2017 12:39 AM
Esteemed Advisor
Posts: 5,399

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

Posted in reply to FoxMulder

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;
PG
Occasional Contributor
Posts: 6

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

Thank you, your solution solved the problem!

 

Sorry for my late reply

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 127 views
  • 0 likes
  • 2 in conversation