DATA Step, Macro, Functions and more

ERROR: Variable MORTGAGE has been defined as both character and numeric.

Reply
anonymous_user
Posts: 0

ERROR: Variable MORTGAGE has been defined as both character and numeric.

Hi, I have the error message below, how do I get around this please?

 

MPRINT(POOTS): ;

MPRINT(POOTS): data pool_codes_201609;

MPRINT(POOTS): set cvc_201609 summit_201609;

ERROR: Variable MORTGAGE has been defined as both character and numeric.

MPRINT(POOTS): run;

 

 

 

%macro poots(start=01jul2006,

stop=%sysfunc(intnx(month,%sysfunc(today()),-1),date9.));

%local i;

%do i=-%sysfunc(intck(month,"&start."d,%sysfunc(today()))) %to -%sysfunc(intck(month,"&stop."d,%sysfunc(today())));

%let poots=%sysfunc(intnx(month,%sysfunc(today()),&i.),yymmn6.);

 

libname poots "&fileLoc./&poots." access=readonly;

proc import datafile = "&fileLoc./&poots./Pool_Code_CVC_&poots..csv" out = cvc_&poots.

replace;

getnames=yes;

GUESSINGROWS=32767;

run;

proc import datafile = "&fileLoc./&poots./Pool_Code_Summit_&poots..csv" out = summit_&poots.

replace;

getnames=yes;

GUESSINGROWS=32767;

run;

data pool_codes_&poots.;

set cvc_&poots. summit_&poots.;

run;

%end;

run;

%mend;

 

options mprint nosymbolgen nomlogic DKRICOND=warn;

%let fileLoc=&scantsloc./Reporting/Mortgage Data Extracts/Encumbrance/Data;

%poots (start=01sep2014, stop=01sep2014);

%poots (start=01dec2014, stop=01dec2014);

%poots (start=01feb2015, stop=01sep2016);

Super User
Posts: 7,763

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user

It looks like the problem happens here:

data pool_codes_&poots.;
set cvc_&poots. summit_&poots.;
run;

The variable is numeric in one dataset and character in the other.

 

Copy the data steps that proc import has created from the log, and adapt them to your needs. In particular make sure that mortgage is of the same type in both steps.

Proc import is a helper for initial tries, but it has to make guesses, and often those fail.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
anonymous_user
Posts: 0

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to KurtBremser

Thanks Kurt, I have tried to do this -

 

data pool_codes_&poots.;

set

cvc_&poots. (keep=mortgage $10. sys legalentity pool_code)

summit_&poots. (keep=mortgage $10. sys legalentity pool_code);

run;

 

but now I error with this -

 

MPRINT(POOTS): ;

MPRINT(POOTS): data pool_codes_201409;

214: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.

ERROR 214-322: Variable name $10. is not valid.

23: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.

ERROR 23-7: Invalid value for the KEEP option.

NOTE: Line generated by the invoked macro "POOTS".

108 $10. sys legalentity pool_code); run;

____

214

23

MPRINT(POOTS): set cvc_201409 (keep=mortgage $10. sys legalentity pool_code) summit_201409 (keep=mortgage $10. sys legalentity

pool_code);

MPRINT(POOTS): run;

 

 

Super User
Posts: 7,763

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user

You're trying to change the symptom, not the cause.

Once again: take the data steps that proc import created and use those to read your external files; set mortgage to the same type in both steps.

Even better: write the data steps for the import yourself, following the documented structure of the csv files. That way you completely avoid guessing.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,763

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

[ Edited ]
Posted in reply to anonymous_user

PS the keep and drop lists only allow variable names, not additional attributes; you cannot change variables there. Neither attributes nor values.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
anonymous_user
Posts: 0

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to KurtBremser

I can't work out what is needed. I have tried multiple things but nothing gives me what I need.

Super User
Posts: 7,763

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user

When you run proc import on a text file, it creates a data step to read that text file. You find that data step in the log.

Copy that piece of the log, apply your macro variables as needed, and use this in the macro instead of proc import.

Make sure that both data steps for both files/datasets in the macro create the same structure. Or at least don't have a variable in common with different types.

 

If you run into problems because you want to read numeric values, but have certain non-numeric data in the input file (ie 'NA' for a missing value), you might have to read into a temporary character variable and convert accordingly.

 

My preferred path of development would be:

 

- run proc import on one dataset

- take the data step and make adaptions

- repeat for the second input dataset

- test the datastep that does the concatenation

 

once that has worked, replace the parts that need to vary with macro variables, set the macro variables manually, and test again.

 

once that works, wrap into a macro for the %do loop

 

You see that the macro part is dead last in my list, for a reason.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 136

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user

Concatenation using the SET statement is sensitive to the TYPE of vaiable in the datasets. In your case variable defined as numeric in one dataset and character in another dataset.

 

Use PROC APPEND with FORCE option.

Thanks,
Suryakiran
Super User
Posts: 7,763

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to SuryaKiran

SuryaKiran wrote:

Concatenation using the SET statement is sensitive to the TYPE of vaiable in the datasets. In your case variable defined as numeric in one dataset and character in another dataset.

 

Use PROC APPEND with FORCE option.


Which is another bad idea, because it once again treats symptoms and not the cause.

Example:

data test1;
input x1 x2;
cards;
1 1
2 2
3 3
;
run;

data test2;
input x1 x2 $;
cards;
4 4
5 NA
6 6
;
run;

proc append data=test2 base=test1 force;
run;

proc print; run;

Take a look at the log and output. Data loss guaranteed.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,498

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user

Brandon,

 

If you're still having trouble getting started on this, here are some basic steps you can take.  These aren't a solution, but they help isolate where the problem lies so you can decide what you would like to happen.

 

You have two SAS data sets you are trying to combine.  Run a PROC CONTENTS on each, and find out which one has MORTGAGE as a character variable and which one has MORTGAGE as a numeric variable.

 

For the data set that has a character MORTGAGE variable, find out why SAS constructed it that way.  Here is a way to subset the observations, so you can see what caused SAS to consider MORTGAGE to be character:

 

proc print data=have;

var mortgage;

where mortgage > ' ' and input(mortgage, ??15.) = .;

run;

 

Most likely, you will see a few observations with unusual values for MORTGAGE, values which cannot be considered to be numeric.  You will have to decide what should be done with those before anyone can design a solution.

Frequent Contributor
Posts: 136

Re: ERROR: Variable MORTGAGE has been defined as both character and numeric.

Posted in reply to anonymous_user


Find which has numeric values.
For example if set cvc_&poots. has numeric values in variable mortagage then:

 

data set cvc_&poots. ;

set set cvc_&poots.(rename=(mortgage=mortgage1)) ;

mortgage=put(mortgage1,10.);

drop mortgage1;

run;

data pool_codes_&poots. ;

set cvc_&poots. summit_&poots.;

run;

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 10 replies
  • 387 views
  • 0 likes
  • 4 in conversation