topic Re: Merge different types and different length of tables in SAS Programming

Merge different types and different length of tables

sarahzhou — Sat, 30 Jul 2022 05:38:02 GMT

Hi,

I have two tables: orders, and returns

orders

v1	v2	v3	v4	v5
Orders	Coffee Cup	Amazon	2022-02-01	US

returns

v1	v2	v3	v4	v5	v6
Return	Coffee Cup	East	Sales	2022-03-01	1

I expect to get result like:

However, after ran the code:

data want;
SET orders returns;
BY v2;
run;

I got:

the v5 value in the Return category has been truncated automatically.

And I tried again by setting the length to a bigger value:

data want;
Length v1 $100 v2 $100 v3 $100 v4 $100 v5 $100 v6 $100;
SET orders returns;
BY v2;
run;

and it still gives me the same output.

Kindly advise.

Thanks

Kurt_Bremser — Sat, 30 Jul 2022 05:42:36 GMT

Pleae post the complete log of your step.

Ksharp — Sat, 30 Jul 2022 10:14:05 GMT

After change the length of variables , you also need to change their FORMAT.

data want;
Length v1 $100 v2 $100 v3 $100 v4 $100 v5 $100 v6 $100;
SET orders returns;
BY v2;
format _character_ $200.;
run;

sarahzhou — Sat, 30 Jul 2022 11:15:51 GMT

thanks @Ksharp , it worked.

Kurt_Bremser — Sat, 30 Jul 2022 13:03:44 GMT

I should have caught what @Ksharp found.

But instead of setting a format (which can get you into the same trouble later), just remove them all:

data want;
length v1 $100 v2 $100 v3 $100 v4 $100 v5 $100 v6 $100;
set orders returns;
by v2;
format _character_;
run;