topic Re: Converting variables to character when stacking datasets in SAS Programming

Converting variables to character when stacking datasets

Sas_user987 — Wed, 03 Feb 2021 18:39:36 GMT

Hi,

I need to stack datasets on top of each other

data output;

set dataset1 dataset2 dataset3 dataset4;

run;

All these dataset have same column names, but because they are coming from different sources, some of them are numeric where it could be a character in the other one.

How can I tell SAS to stack dataset together but tat the same time convert all variables into character?

Thanks,

Justinas

Re: Converting variables to character when stacking datasets

RichardDeVen — Wed, 03 Feb 2021 19:22:18 GMT

There is no built in feature that will stack variables of same name but different types.

The template for creating a proper target character variable in the stacked result set is to ensure the width (variable length) is wide enough and the numeric variables are transformed with a PUT using the appropriate FORMAT or some other mechanism.

Example:

Use VVALUE function to retrieve the formatted value of a numeric variable.

data want;
  length x $30;
  set 
    source_1 (in=_1)
    source_2 (in=_2 rename=(x=x_num2))
    source_3 (in=_3 rename=(x=x_num3))
  ;

  select;
    when (_1) ; 
    when (_2) x = vvalue(x_num2);
    when (_3) x = vvalue(x_num3);
    otherwise;
  end;

  drop x_num2 x_num3;
run;

Once you understand the coding pattern you can use meta data to compute the source code needed into macro variable(s) that would then be resolved in a skeleton or template DATA step that relies on the macro system resolving the computed source code(s).

Re: Converting variables to character when stacking datasets

mkeintz — Wed, 03 Feb 2021 22:35:39 GMT

Are these ID numbers, or other values that will never be subject to mathematical operations (average, sum, ... )? And even if they are ID numbers, consider writing them to character values with leading zeroes, if necessary. Otherwise a different sort order would be produced (i.e. '54' would be greater than '122', but '054' would not).

Re: Converting variables to character when stacking datasets

Kurt_Bremser — Thu, 04 Feb 2021 06:47:26 GMT

Define "different sources".

If you get your data from separate (business) entities in different formats (e.g. xlsx vs. text), then you should streamline your orocess by setting up agreements with a unified format for data transfer files.

If it's just multiple files in the same format, then the use of PROC IMPORT is the most likely culprit.