SAS transpose multiple variables wide to long

[darkmoon178]

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 🙂

[novinosrin · Accepted solution]

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;

[darkmoon178]

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;

[novinosrin]

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!

[singhsahab]

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;

[acerickson]

thanks for posting a Macro solution. Much appreciated!

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

[Astounding]

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;