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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

sagulolo
Quartz | Level 8

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.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sagulolo
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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

sagulolo
Quartz | Level 8

Hi RW9 and Ballardw,

 

My deepest apologize for so late response and reply.

 

Thanks for your time and effort for highlighted issue and suggested solution.

if you don't mind, hope you can share/hint me some macro coding to perform below, for self learning purpose.


1. check if variable is numeric/character
2. if numeric then change to character else do nothing

 

Once again, really thank for your all hard work, advice and opinions, it help me a lot and this website will almost be my all time favorite check link although i seldom post question but i will gain more knowledge by read through other user question and suggested solution. 

 

Have a nice weekend and keep up the good work.

 

Best Regards

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I repeat my previous statement:

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

 

Macro only deals with character data, it is a find/replace system, nothing more.  I would need some examples of what you mean, test data/expected output, but a simple datastep should suffice.  Please open a new topic with the relevant details.

sagulolo
Quartz | Level 8

Hi RW9,

 

Understood and thank you, sir.

 

Best Regards.

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
  • 9 replies
  • 984 views
  • 1 like
  • 3 in conversation