## Reshaping from long to wide

Solved
Regular Contributor
Posts: 249

# Reshaping from long to wide

[ Edited ]

Hi everyone

Can I please ask your help on how can I improve my SAS code to make it shorter?

I would like to reshape from a long data to wide.

Thank you heaps.

 Long id memberid type period 1 1 2 1 1 1 6 3 1 1 1 1 1 2 7 2 1 2 7 1 1 2 8 4 1 2 3 4

 Wide id memberid B_1 F_3 A_1 G_2 G_1 H_4 C_4 1 1 1 1 1 0 0 0 0 1 2 0 0 0 1 1 1 1

`data wide set long; if type=1 and period=1 then output A_1; if type=1 and period=2 then output A_2; if type=1 and period=3 then output A_3; if type=1 and period=4 then output A_4; . . . if type=26 and period=1 then output Z_1; if type=26 and period=2 then output Z_2; if type=26 and period=3 then output Z_3; if type=26 and period=4 then output Z_4;run;data wide2;  merge A_1(in=a) A_2(in=aa) A_3(in=aaa) A_4(in=aaaa)  ... Z_1(in=Z) Z_2(in=ZZ) Z_3(in=ZZZ) Z_4(in=ZZZZ); by id memberid; if a then A_1=1; else A_1=0; . . . if ZZZZ then Z_4=1; else Z_4=0;run;`

Accepted Solutions
Solution
‎10-03-2016 02:26 AM
Super User
Posts: 23,320

## Re: Reshaping from long to wide

[ Edited ]

In your long format create a new variable that maps the number to the letter. You'll need to do a pass afterwards to set missing to 0.

Heres a mock up minus that last step. This is untested but should be pretty close. The byte function should return the correct alphabet but you may need tweak depending on your system.

``````Data want;
Set have;
Group=catx('_', byte(64+type), period);
Count=1;
Run;

Proc sort data = have;
By ID memberID group count;
run;

proc transpose data=have out=want;
by id memberid;
var count;
id group;
run;``````

All Replies
Posts: 4,680

## Re: Reshaping from long to wide

Is the value of Type used to determine if output goes to "A" or "Z"? (type=1 -> A, type=2 -> B,...) ?

Regular Contributor
Posts: 249

## Re: Reshaping from long to wide

Hi @Patrick.

i have just corrected my post.

Yes, type 1 = A....., type 26 = Z.

Posts: 4,680

## Re: Reshaping from long to wide

What you ask for can be done BUT I believe the naming convention you're using will make it very cumbersome in any downstream code to address the variables. I'd go for some naming convention which would make it easier to address the variables for an array using wildcard syntax.

But o.k. - I'll post some code once I have it "for the fun of it".

Solution
‎10-03-2016 02:26 AM
Super User
Posts: 23,320

## Re: Reshaping from long to wide

[ Edited ]

In your long format create a new variable that maps the number to the letter. You'll need to do a pass afterwards to set missing to 0.

Heres a mock up minus that last step. This is untested but should be pretty close. The byte function should return the correct alphabet but you may need tweak depending on your system.

``````Data want;
Set have;
Group=catx('_', byte(64+type), period);
Count=1;
Run;

Proc sort data = have;
By ID memberID group count;
run;

proc transpose data=have out=want;
by id memberid;
var count;
id group;
run;``````
Posts: 4,680

## Re: Reshaping from long to wide

Not as elegant as @Reeza's approch but should work:

``````data have;
input id memberid type period;
datalines;
1 1 2 1
1 1 6 3
1 1 1 1
1 2 7 2
1 2 7 1
1 2 8 4
1 2 3 4
;
run;

/* generate macro variable containing all possible variable names A_1 to Z_4 */
data _null_;
length string \$32767;
pos_a=rank('A');
do i=pos_a to (pos_a+25);
do period='1','2','3','4';
string=catx(' ',string,(byte(i)||'_'||period));
end;
end;
call symputx('vars',string);
call symputx('pos_a',pos_a);
run;

/* create wide data set WANT */
data want(drop= _: type period);
set have;
by id memberid;

array vars {26,4} 8 &vars;
retain vars;

if first.memberid then
do;
/* initialize vars in array to zero */
do _i=1 to dim(vars);
do _j=1 to 4;
vars[_i,_j]=0;
end;
end;
end;

vars[type,period]=1;

if last.memberid then output;
run;
``````
Regular Contributor
Posts: 249

## Re: Reshaping from long to wide

Dear @Reeza and @Patrick, thank you for your help.

Posts: 4,680

## Re: Reshaping from long to wide

...and just because I believe your naming condition will make coding hard for you below some code which generates variables ind_1 to ind_104 but then creates labels for the vars. This way the variables as such will be easily addressable in code but you still got descriptive labels for printing.

``````data have;
input id memberid type period;
datalines;
1 1 2 1
1 1 6 3
1 1 1 1
1 2 7 2
1 2 7 1
1 2 8 4
1 2 3 4
;
run;

filename codegen temp;
data inter(keep=ind_:);

/* create variables */;
array ind_ {26,4} (104*0);
output;

/* generate code for attrib statement for variables */
file codegen;
put "attrib ";
do _i=1 to dim1(ind_);
do _j=1 to dim2(ind_);
varname=vname(ind_[_i,_j]);
put varname "label='type: " _i ", period: " _j "'";
end;
end;
put ";";

stop;
run;

/* execute generated code: assign labels to variables */
data inter;
set inter;
%include codegen / source2;
run;
filename codegen;

/* create wide data set WANT */
data want(keep=id memberid ind_:);
set have;
by id memberid;

retain _i 1;
set inter point=_i;

array ind_vars {26,4} ind_:;

ind_vars[type,period]=1;

if last.memberid then output;
run;

proc print data=want(keep=id memberid ind_1-ind_10) label;
run;
``````
Regular Contributor
Posts: 249

## Re: Reshaping from long to wide

Thank you @Patrick. I would also like to accept yours as solution if only possible

☑ This topic is solved.