Help using Base SAS procedures

Transpose of Data

Reply
Super Contributor
Posts: 265

Transpose of Data

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

Super User
Super User
Posts: 7,430

Re: Transpose of Data

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;

Super Contributor
Posts: 336

Re: Transpose of Data

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;

Super User
Posts: 9,691

Re: Transpose of Data

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

Super Contributor
Posts: 265

Re: Transpose of Data

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 ?

Respected Advisor
Posts: 3,777

Re: Transpose of Data

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;
Super Contributor
Posts: 265

Re: Transpose of Data

Hi , Thanks a lot,

I have a query on it, what dummy for?

Thanks

Respected Advisor
Posts: 3,777

Re: Transpose of Data

Remove it and you will see.

Super Contributor
Posts: 265

Re: Transpose of Data

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

and can we getridof first heading "Area"?

Respected Advisor
Posts: 3,777

Re: Transpose of Data

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.

Super Contributor
Posts: 265

Re: Transpose of Data

yaa, its true , its magical.

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

thanks

Super User
Posts: 3,115

Re: Transpose of Data


Just add a blank label to Area:

define area / ' ' across order=data;

Super User
Posts: 9,691

Re: Transpose of Data

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

Ask a Question
Discussion stats
  • 12 replies
  • 621 views
  • 0 likes
  • 6 in conversation