topic Re: combine datasets if different datatypes in SAS Programming

combine datasets if different datatypes

BrahmanandaRao — Wed, 08 Nov 2023 10:03:37 GMT

data dsn1;
input id score;
datalines;
1 22
1 55
2 55
4  .
4  44
;
run;


data dsn2;
input id $ score;
datalines;
6 55 
6  .
7 12
8  44
9  66
9 14
;
run;

data con_to_numeric;
set  dsn2;
id1=input(id ,best8.);
rename id1= id;
run;

data combine;
set dsn2 con_to_numeric;
run;

how to combine dataset if different datatypes in datastep and proc sql

Re: combine datasets if different datatypes

Astounding — Wed, 08 Nov 2023 10:14:54 GMT

Your sample program would work except for two small errors.

First, the logic for con_to_numeric creates two variables both named ID. That's not allowed. To fix that, add before the RENAME statement:

drop id;

Second, your last SET statement reads the wrong data sets. It should say:

set dsn1 con_to_numeric;

Re: combine datasets if different datatypes

ChanceTGardener — Wed, 08 Nov 2023 12:31:15 GMT

You can append data sets using the OUTER UNION CORRESPONDING statement in PROC SQL.

In work.ds2, the id column is converted from character to numeric using the input() function.

proc sql;
 create table work.want as 
  select *
  from dsn1
    outer union corresponding
  select input(id,1.) as id
  ,		 score
  from dsn2
 order by id;
quit;

Re: combine datasets if different datatypes

Kurt_Bremser — Wed, 08 Nov 2023 12:53:58 GMT

Do it in one step:

data want;
set
  dsn1 (
    in=in1
    rename=(id=id1)
  )
  dsn2
;
if in1 then id = input(id1.best.);
drop id1;
run;

But with ID's you should convert everything to character with formatting as documented for your data.

Re: combine datasets if different datatypes

BrahmanandaRao — Mon, 13 Nov 2023 10:36:24 GMT

Hi @ChanceTGardener

Thank you for your solution