topic Re: SAS transpose multiple variables wide to long in SAS Programming

SAS transpose multiple variables wide to long

darkmoon178 — Sat, 06 Oct 2018 21:28:57 GMT

Hi everyone,

I'm new with SAS and I am trying to transpose multiple variable from wide to long, as in the example mention below

The A,B,C is the prefix followed by time (year).

ID Name FirmID A1981 A1982 ... B1981 B1982... C1981 C1982...

1 x 123 2 3 4 5 6 7

2 y 124 22 33 44 55 66 77

3 z 555 222 333 444 555 666 777

I don't know how to use prefix (A,B,C) and transpose by year. In addition, is there any code so that I can combine to transpose to multiple variables by year, or I have to do that for 3 times from wide to long (one for A, one for B, and one for C)?

This is the data I want to get:

ID Name FirmID Year A B C

1 x 123 1981 2 4 6

1 x 123 1982 3 5 7

.......

2 y 124 1981 22 44 66

2 y 124 1982 33 55 77

.........

3 z 555 1981 222 444 666

3 z 555 1982 333 555 777

I'm appreciate any help 🙂

Re: SAS transpose multiple variables wide to long

novinosrin — Sat, 06 Oct 2018 22:13:59 GMT

Hi @darkmoon178, seems your 1st post, so welcome to the sas forum.

First off, Below is an easy yet inefficient solution as I do have a one step datastep based solution in my mind, however feeling lazy at 5:15 pm Saturday to pen that down. I am sure somebody will post the efficient solution shortly. Until then you can play with mine.

data have;
input ID $  Name $   FirmID $   A1981   A1982  B1981   B1982 C1981  C1982;
cards;
1     x           123         2           3             4             5            6            7
2     y           124        22         33           44           55         66           77
3     z           555       222        333         444         555        666       777
;
 
proc transpose data=have out=t;
by id name firmid;
var _numeric_;
run;
data t1;
set t;
year=input(compress(_name_,,'kd'),8.);
v=first(_name_);
run;

proc sort data=t1 out=t2;
by id name firmid year;
run;

proc transpose data=t2 out=want(drop=_name_);
by id name firmid year;
var col1;
id v;
run;

Re: SAS transpose multiple variables wide to long

darkmoon178 — Sat, 06 Oct 2018 22:21:05 GMT

Thank you so much. I really appricate your help and I will try yours

I also figure out use these codes:

proc transpose data=data out=data1 prefix=A;
by id name FirmID;
var A1981-A1982;
run;

proc transpose data=data out=data2 prefix= B;
by id name FirmID;

var B1981-B1982;
run;

proc transpose data=data out=data3 prefix= C;
by id name FirmID;

var C1981-C1982;
run;

data datafinal;
merge data1 (rename=(A1=A) drop = _name_)
data2 (rename=(B1=B) drop =_name_)
data3 (rename=(C1=C));
by id name FirmID;

year=input(substr(_name_, 5),5.);
drop _name_ _label_;
run;

Re: SAS transpose multiple variables wide to long

novinosrin — Sat, 06 Oct 2018 22:25:44 GMT

Good try and your effort is well appreciated.

However

var A1981-A1982;

is hard coding and potentially tons of it may arise in a typical production automated environment. So speed up your learning imho. Have fun!

Re: SAS transpose multiple variables wide to long

Astounding — Sat, 06 Oct 2018 22:26:40 GMT

To avoid multiple steps, you would need to do a little coding. I'm not sure how many variables you actually have here, so I will illustrate with three sets.

data want;

set have;

array newvars {3} A B C;

array oldvars {3, 1981:1982} A1981 A1982 B1981 B1982 C1981 C1982;

do year = 1981 to 1982;

do k=1 to 3;

newvars{k} = oldvars{k, year};

end;

output;

end;

keep id Name FirmID A B C;

run;

It's untested, so you'll need to verify whether I got the array elements in the proper order.

While it wouldn't be as sophisticated, it's nearly as easy just hard-coding the whole thing:

data want;

set have;

Year = 1981;

A = A1981;

B = B1981;

C = C1981;

output;

Year = 1982;

A = A1982;

B = B1982;

C = C1982;

output;

keep ID Name FirmID Year A B C;

run;

Re: SAS transpose multiple variables wide to long

singhsahab — Sun, 07 Oct 2018 03:33:57 GMT

Hello Dear,

Just simplified your code by macro .

data have;
input
ID & Name & $ FirmID & A1981 & A1982 & B1981 & B1982 & C1981 & C1982 &;
datalines;
1 x 123 2 3 4 5 6 7

2 y 124 22 33 44 55 66 77

3 z 555 222 333 444 555 666 777
;
run;

%macro tran(out=,var_v=,pre_v=);
proc transpose data=have out=&out (rename=(&var_v=&pre_v _name_=year)) prefix=&pre_v;
by id name firmid;
var &var_v:;
run;
%mend;

/*Calling Macro*/

%tran(out=a_dataset,var_v=a1,pre_v=a);
%tran(out=b_dataset,var_v=b1,pre_v=b);
%tran(out=c_dataset,var_v=c1,pre_v=c);

data want;
merge a_dataset b_dataset c_dataset;
by id name firmid;
year=input(substr(year,2),8.);
run;

Re: SAS transpose multiple variables wide to long

acerickson — Fri, 02 Aug 2019 19:03:47 GMT

thanks for posting a Macro solution. Much appreciated!

I think there is an error in the data creation code, but the macro works perfect.