BookmarkSubscribeRSS Feed

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

10 REPLIES 10
Kurt_Bremser
Super 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.

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;

 

 

Kurt_Bremser
Super 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.

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

Kurt_Bremser
Super 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.

SuryaKiran
Meteorite | Level 14

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
Kurt_Bremser
Super User

@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.

 

Astounding
PROC Star

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.

SuryaKiran
Meteorite | Level 14


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

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
  • 10 replies
  • 1260 views
  • 0 likes
  • 4 in conversation