DATA Step, Macro, Functions and more

Macro to combine datasets into one datasets but error due variable define both character and numeric

Reply
Highlighted
Contributor
Posts: 63

Macro to combine datasets into one datasets but error due variable define both character and numeric

Dear All, 

 

Need your expertise/guidance on the above subject issue, I have total of 19 datasets which i need to combine then into one datasets, example as below:-

 

datasets_201701

datasets_201702

datasets_201703

and so on

 

I have the following macro code which able to combine my other datasets but no the above one due to one of the variable(example acctno) was define as numeric and character. How could I achieve this in macro environment as for data step i know just add below code will do:-

 

data test(drop=acctno rename=(acctno_=acctno));
	set source_dataset;
	if vtype(acctno)='N' then 
		acctno_ = put(acctno, 15.);
	else
		acctno_ = acctno;
run;

Below is my macro code. Your advice/suggestion is much appreciated.

 

libname EXT 'C:\Users\Desktop';

/* Run a PROC CONTENTS to create a SAS data set with the names of the SAS data sets in the SAS data 
library */

proc contents data=EXT._all_ out=EXT_cont(keep=memname) noprint;
run;
	
/* Eliminate any duplicate names of the SAS data set names stored in the SAS data set */

proc sort data=EXT_cont nodupkey;
by memname;
run;

data _null_;
  set EXT_cont end=last;
  by memname;
  i+1;
  call symputx('name'||trim(left(put(i,8.))),memname);
  if last then call symputx('count',i);
run;
;


%macro append;
data results;
set 
%do i=1 %to &count;
	EXT.&&name&i
%end;
;
run;
%mend append;

%append;

**for your information, some amendment on my code for security reason, apologize for any inconvenience caused.

 

Super User
Super User
Posts: 9,789

Re: Macro to combine datasets into one datasets but error due variable define both character and num

Advice: You do not need macro, you never need macro.

 

Look at the proc append command: 

http://support.sas.com/kb/25/105.html

It is specifically designed to append data.

 

You could also do it by:

data want;
  set datasets_:;
run;

As your data has the same prefix.

 

Now onto your error.  The reason you have got this, and it is common, is that you are using proc import to import excel files.  This is a really bad idea.  Excel is a bad data format, and proc import is guessing procedure, hence you end up with all kinds of mistakes.  Go back and fix your import procedure, use CSV or something like that, and write a datastep to import it correctly.  This is how proper data transfer happens and deviating from that will make you have these headaches and extra processing code.

 

Contributor
Posts: 63

Re: Macro to combine datasets into one datasets but error due variable define both character and num

HI RW9,

 

Thank for your time.

 

Yes, you right, just use set datasets_:; will do. For the source file, actually is a SAS data set format (.sas7bdat) which export from other party with different type in variable (acctno). This is the copy of data available at the moment as I not able to access the source system/file.

 

 

Super User
Super User
Posts: 9,789

Re: Macro to combine datasets into one datasets but error due variable define both character and num

"export from other party" - I have yet to come across a system which exports the data in different random formats other than Excel.  Sounds like that system is broken.  Up to you whether your time and resource is less valuable to fix it your end.

Contributor
Posts: 63

Re: Macro to combine datasets into one datasets but error due variable define both character and num

Hi RW9,

 

Apologize for my poor language, actually the dataset was given by other dept(using SAS also), but not sure why the type change from time to time. Maybe there are do some data manipulation and just drop of the type and cause this data type inconsistent.

Super User
Super User
Posts: 9,789

Re: Macro to combine datasets into one datasets but error due variable define both character and num

Point it out to them, maybe they haven't noticed (or are using Excel).  You can change it your end of course, its your resource.  But be careful, if your code messes up and converts it incorrectly...

Hope I answered your question with the prefix: command.

Super User
Posts: 13,868

Re: Macro to combine datasets into one datasets but error due variable define both character and num


@sagulolo wrote:

Hi RW9,

 

Apologize for my poor language, actually the dataset was given by other dept(using SAS also), but not sure why the type change from time to time. Maybe there are do some data manipulation and just drop of the type and cause this data type inconsistent.


I would suspect that other department is bringing data into SAS format from a different file format and using Proc Import, or a wizard that calls proc import, which is guessing at the content each and every time a file is read.

If the file source is Excel then only 8 or so lines are examined to guess the contents. Due to the way many people use Excel that often does not accurately determine lengths of variables and whether a value should be numeric or character.

 

Any process relying on Proc Import will have either your issue of variables changing type or truncation of character data as the lengths of character variables change between versions of files read.

 

The "fix" is to make sure that the process bringing data into SAS is under control by reading data in a reliable format, .txt or .CSV files and writing a program to read them all the same way if they have the same layout.

Ask a Question
Discussion stats
  • 6 replies
  • 78 views
  • 0 likes
  • 3 in conversation