BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FoxMulder
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
PGStats
Opal | Level 21

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
FoxMulder
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Opal | Level 21

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
FoxMulder
Fluorite | Level 6

Thank you, your solution solved the problem!

 

Sorry for my late reply

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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