SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to append data sets with variables defined as both character and numeric?

Reply
Frequent Contributor
Posts: 75

How to append data sets with variables defined as both character and numeric?

My survey collected data sets at hierarchical locations that I now want to append them. They consist of the same variables for the most part, but there are some variables in this data set while not in others and vice versa, so I intend to use SET statement.

My question arises because, when importing data from Excel, there are a few variables defined as numeric in some data sets and character in others, perhaps due to data entry process. This in turn makes the program not work.

So is there a remedy to this problem?

Super User
Posts: 19,810

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to NonSleeper

Make them all the same. Unfortunately that's a manual process, best done in the import step in my opinion.

Super User
Posts: 10,028

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to NonSleeper

The most convenient way is use SQL . SAS will take care of everything.

proc sql;

create table want as

select * from one

  union all

select * from two ;

quit;

Super User
Posts: 7,782

Re: How to append data sets with variables defined as both character and numeric?

Won't work.

data have1;

x = 'X';

run;

data have2;

x = 1;

run;

proc sql;

select * from have1 union all select * from have2;

quit;

produces

ERROR: Column 1 from the first contributor of UNION ALL is not the same type as its counterpart from the second.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,028

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to KurtBremser

Sorry about that . I must be confused about something

Super User
Posts: 7,782

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to NonSleeper

Set up a solid import process from Excel (best with .csv as transport file format) and determine the variable type in the data step that reads the data. Then everything else in SAS is a breeze.

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

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to NonSleeper

Quite agree with KurtBremser, your problem is with using Excel not with SAS.  Excel is not a tool for <insert whatever you think to do with Excel here> and as such will cause you these kinds of headaches.  I would suggest, rather than exporting to CSV, you re-assess your source data (as that is now what your Excel workbook is) and by source data you would go through the steps of validating the software install, applying appropriate data-management to the workbook, keeping an audit history on the file etc. much like any data capture software (DB etc.) will do as they are applications written to do data capture.  Once you have run over the data cleaning in the sheet, ensured that everything matches, then you can export to CSV and import into SAS.

Frequent Contributor
Posts: 115

Re: How to append data sets with variables defined as both character and numeric?

Posted in reply to NonSleeper

Hi Non sleeper, If you are still awake, my suggestion would be to convert the numeric variables to character in one of your datasets using the below macro and append using Xia Keshan's code: union all.

So, you will have all variables as characters and as Kurt says it's now a breeze. But hey, for your future quantitave processing, you will have to convert back to numerics

%macro vars(dsn);

  %let list=;
  %let type=;
  %let dsid=%sysfunc(open(&dsn));
  %let cnt=%sysfunc(attrn(&dsid,nvars));
   %do i = 1 %to &cnt;
    %let list=&list %sysfunc(varname(&dsid,&i));
    %let type=&type %sysfunc(vartype(&dsid,&i));
   %end;
  %let rc=%sysfunc(close(&dsid));

  data want(drop=
    %do i = 1 %to &cnt;
     %let temp=%scan(&list,&i);
       _&temp
    %endSmiley Wink;
   set &dsn(rename=(
    %do i = 1 %to &cnt;
     %let temp=%scan(&list,&i);
       &temp=_&temp
    %endSmiley Wink);
    %do j = 1 %to &cnt;
     %let temp=%scan(&list,&j);
   /** Change C to N for numeric to character conversion  **/
     %if %scan(&type,&j) = N %then %do;
   /** Also change INPUT to PUT for numeric to character  **/
      &temp=PUT(_&temp,8.);
     %end;
     %else %do;
      &temp=_&temp;
     %end;
    %end;
  run;

%mend vars;

%vars(Your_input_dataset_name)

Regards,

Naveen Srinivasan

L&T Infotech

Ask a Question
Discussion stats
  • 7 replies
  • 706 views
  • 7 likes
  • 6 in conversation