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

[NonSleeper]

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?

[Reeza]

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

[Ksharp]

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;

[Kurt_Bremser]

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.

[Ksharp]

Sorry about that . I must be confused about something

[Kurt_Bremser]

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.

[RW9]

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.

[naveen_srini]

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
    %end;);
   set &dsn(rename=(
    %do i = 1 %to &cnt;
     %let temp=%scan(&list,&i);
       &temp=_&temp
    %end;));
    %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