topic Re: Transpose of Data in SAS Procedures

Transpose of Data

Aman4SAS — Thu, 14 Aug 2014 06:11:09 GMT

Hi have a dataset : like

order period area sales comm

1 jan north 45 25

1 feb north 44 44

1 Mar north 75 25

1 apr north 41 35

1 may north 65 55

1 jun north 45 15

2 jan South 45 25

2 feb South 44 44

2 Mar South 75 25

2 apr South 41 35

2 may South 65 55

2 jun South 45 15

3 jan East 45 25

3 feb East 44 44

3 Mar East 75 25

3 apr East 41 35

3 may East 65 55

3 jun East 45 15

4 jan West 45 25

4 feb West 44 44

4 Mar West 75 25

4 apr West 41 35

4 may West 65 55

4 jun West 45 15

My desire output in excel is: Variable's position order as order var

North South East West

1 jan 45 25 45 25 45 25 45 25

1 feb 44 44 44 44 44 44 44 44

1 Mar 75 25 75 25 75 25 75 25

1 apr 41 35 41 35 41 35 41 35

1 may 65 55 65 55 65 55 65 55

1 jun 45 15 45 15 45 15 45 15

please help

Re: Transpose of Data

RW9 — Thu, 14 Aug 2014 08:29:37 GMT

Hi,

/* Note not tested */

proc sort data=have out=a (keep=order period area sales);

by order period area;

run;

proc sort data=have out=b (keep=order period area comm);

by order period area;

run;

proc transpose data=a out=t_a;

by order period;

var sales;

id area;

run;

proc transpose data=b out=t_b;

by order period;

var sales;

id area;

run;

proc sql;

create table WANT as

select COALESCE(A.ORDER,B.ORDER) as ORDER,

COALESCE(A.PERIOD,B.PERIOD) as PERIOD,

A.NORTH as A_NORTH,

B.NORTH as B_NORTH,

A.SOUTH as A_SOUTH,

B.SOUTH as B_SOUTH,

A.EAST as A_EAST,

B.EAST as B_EAST,

A.WEST as A_WEST,

B.WEST as B_WEST

from T_A A

full join T_B B

on A.ORDER=B.ORDER

and A.PERIOD=B.PERIOD;

quit;

Re: Transpose of Data

user24feb — Thu, 14 Aug 2014 09:48:00 GMT

This code is less sophisticated than RW9's code because it requires a dummy variable, but it might do the job:

Data A;
Input order period $ area $ sales comm;
Datalines;
1 jan north 45 25
1 feb north 44 44
1 Mar north 75 25
1 apr north 41 35
1 may north 65 55
1 jun north 45 15
2 jan South 45 25
2 feb South 44 44
2 Mar South 75 25
2 apr South 41 35
2 may South 65 55
2 jun South 45 15
3 jan East 45 25
3 feb East 44 44
3 Mar East 75 25
3 apr East 41 35
3 may East 65 55
3 jun East 45 15
4 jan West 45 25
4 feb West 44 44
4 Mar West 75 25
4 apr West 41 35
4 may West 65 55
4 jun West 45 15
;
Run;

Proc Sort Data=A;
By Order Period Area;
Run;

Proc Transpose Data=A
Out=A_Vector;
By Order Period Area;
Var Sales Comm;
Run;

Data A_Vector;
Set A_Vector;
Area_Var=Compress(Area!!"_"!!_NAME_);
Run;

Proc Sort Data=A_Vector;
By Period;
Run;

Proc Transpose Data=A_Vector
Out=A_Trans (Drop=_NAME_);
By Period;
Var Col1;
ID Area_Var;
Run;

Re: Transpose of Data

Ksharp — Thu, 14 Aug 2014 12:50:53 GMT

Assuming all the id have the same period .

Data have;
  Input order period $ area $ sales comm;
  Datalines;
1 jan north 45 25
1 feb north 44 44
1 Mar north 75 25
1 apr north 41 35
1 may north 65 55
1 jun north 45 15
2 jan South 45 25
2 feb South 44 44
2 Mar South 75 25
2 apr South 41 35
2 may South 65 55
2 jun South 45 15
3 jan East  45 25
3 feb East  44 44
3 Mar East  75 25
3 apr East  41 35
3 may East  65 55
3 jun East  45 15
4 jan West  45 25
4 feb West  44 44
4 Mar West  75 25
4 apr West  41 35
4 may West  65 55
4 jun West  45 15
;
Run;
proc sql;
create table temp as
select distinct catt('have(rename=(sales=',area,'_sales comm=',area,'_comm) where=(order=',order,' ) )') as data from have;
quit;
data _null_;
 set temp end=last;
if _n_ eq 1 then call execute('data want(drop=area) ; merge ')     ;
call execute(data);
if last then call execute(';run;');
run;

Xia Keshan

Re: Transpose of Data

Aman4SAS — Thu, 14 Aug 2014 13:35:12 GMT

Hi Xia,

Can u please suggest me to resolve one issue with proc sql.

I have a data with order D-C-B-A

when i run this code:

proc sql noprint;

select distinct areaname into : arealist separated by "|" from test ;

%let Ar_ct= &sqlobs;

quit;

%put &arealist &ar_ct;

its giving me arealist : A|B|C|D

I dont want to change existing order, Can it be resolve ?

Re: Transpose of Data

data_null__ — Thu, 14 Aug 2014 13:40:10 GMT

Why not just go directly to EXCEL.

data a;
   input order period :$upcase3. area:$upcase5. sales comm;
   cards; 
1 jan north 45 25
1 feb north 44 44
1 Mar north 75 25
1 apr north 41 35
1 may north 65 55
1 jun north 45 15
2 jan South 45 25
2 feb South 44 44
2 Mar South 75 25
2 apr South 41 35
2 may South 65 55
2 jun South 45 15
3 jan East 45 25
3 feb East 44 44
3 Mar East 75 25
3 apr East 41 35
3 may East 65 55
3 jun East 45 15
4 jan West 45 25
4 feb West 44 44
4 Mar West 75 25
4 apr West 41 35
4 may West 65 55
4 jun West 45 15
;;;;
   run; 
proc print; 
   run; 
ods tagsets.excelxp file='~/compass.xml'; 
proc report nowd missing; 
   column period  area, (sales comm) dummy;
   define period / group order=data;
   define area / across order=data;
   define sales / display; 
   define comm / display; 
   define dummy / noprint; 
   run; 
ods tagsets.excelxp close;

Re: Transpose of Data

Aman4SAS — Thu, 14 Aug 2014 13:49:56 GMT

Hi , Thanks a lot,

I have a query on it, what dummy for?

Thanks

Re: Transpose of Data

data_null__ — Thu, 14 Aug 2014 13:51:11 GMT

Remove it and you will see.

Re: Transpose of Data

Aman4SAS — Thu, 14 Aug 2014 13:56:24 GMT

Its showing error that there is no variable associated with sales:

and can we getridof first heading "Area"?

Re: Transpose of Data

Ksharp — Thu, 14 Aug 2014 14:15:29 GMT

OK. Make an order variable N .

data have;
input group a $ b c;
datalines;
1 A 23 20
1 B 34 32
1 C 29 12
1 D 24 10
;
run;

data have;
 set have;
 if a='A' then n=4;
  else  if a='B' then n=3;
   else  if a='C' then n=2;
    else  if a='D' then n=1;
run;


proc sql;
select distinct a into : list separated by '|' from have order by n;
quit;
%put &list ;

Xia Keshan

Re: Transpose of Data

data_null__ — Thu, 14 Aug 2014 14:26:56 GMT

Now you see that DUMMY is the little trick that makes it all work. It is documented somewhere and I can't remember the "reason" that PROC REPORT works that way but you can look it up.

Re: Transpose of Data

Aman4SAS — Thu, 14 Aug 2014 19:01:26 GMT

yaa, its true , its magical.

can u please help me to getridof first header "Area". i want to remove that.

thanks

Re: Transpose of Data

SASKiwi — Thu, 14 Aug 2014 19:38:40 GMT

Just add a blank label to Area:

define area / ' ' across order=data;