Converting rows into columns

Reply
Frequent Contributor
Posts: 85

Converting rows into columns

I have a sample data set and output format


data sample ;
input emp $ 1-4 sal 5-12 date 13-20;
datalines;
1111 5000 31OCT2013
1112 3200 31OCT2013
1113 4200 31OCT2013
1114 5100 31OCT2013
1115 7200 31OCT2013
1116 6000 31OCT2013
1111 5000 30NOV2013
1112 3200 30NOV2013
1113 4200 30NOV2013
1114 5100 30NOV2013
1115 7200 30NOV2013
1116 6000 30NOV2013
1111 2000 31DEC2013
1112 3200 31DEC2013
1113 5200 31DEC2013
1114 7100 31DEC2013
1115 6200 31DEC2013
1116 2000 31DEC2013

output sample format

emp  sal-31OCT2013 sal-30NOV2013 sal-31DEC2013
1111     5000               5000                         2000
1112     3200               3200                         3200
1113     4200               4200                         5200
1114     5100               5100                         7100
1115     7200               7200                         6200
1116     6000               6000                          2000

date wise salary are in rows i need to put them in columns as month increase rows increase and i need to increase columns

how can i dynamically increace columns and map salary to output

Respected Advisor
Posts: 3,902

Re: Converting rows into columns

First sum "sal' by emp and date (eg. using Proc SQL), then use Proc Transpose.

Apply a format of date9. to 'date' before transposing the table.

Occasional Contributor
Posts: 12

Re: Converting rows into columns

proc sort data=sample out=int;

     by emp;

run;

proc transpose data=int out=want(drop=_Smiley Happy prefix=sal_ ;

     by emp;

     var sal;

     id date;

run;

Frequent Contributor
Posts: 85

Re: Converting rows into columns

if i has 2 columns to be transpose then how

eg.

data sample ;

input emp $ 1-4 Amount   5-10 INCRE 11-15 date ;

informat date date9.;

format date mmyys5.;

datalines;

1111 5000 1200 31OCT2013

1112 3200 1000 31OCT2013

1113 4200 1258 31OCT2013

1114 5100 1258 31OCT2013

1115 7200 1000 31OCT2013

1116 6000 500  31OCT2013

1111 5000 1200 30NOV2013

1112 3200 1400 30NOV2013

1113 4200 1500 30NOV2013

1114 5100 1000 30NOV2013

1115 7200 1000 30NOV2013

1116 6000 1255 30NOV2013

1111 2000 1221 31DEC2013

1112 3200 1200 31DEC2013

1113 5200 1300 31DEC2013

1114 7100 1425 31DEC2013

1115 6200 1221 31DEC2013

1116 2000 1000 31DEC2013

;

run;

sample output

emp   31OCT2013  30NOV2013             31DEC2013        31OCT2013           30NOV2013             31DEC2013      

1111     5000               5000                         2000                  1200                      1200                             1221

1112     3200               3200                         3200                  1000                      1400                              1200

1113     4200               4200                         5200                  1258                      3200                             1300

1114     5100               5100                         7100                  1258                      1500                             1425

1115     7200               7200                         6200                  1000                      1000                             1221

1116     6000               6000                          2000                 500                         1000                              1000

3 Columns are sal and 3 are increment with same date as label

how to transpose 2 variables

Trusted Advisor
Posts: 1,131

Re: Converting rows into columns

Please try ,

proc sort data=sample;

    by emp;

run;

proc transpose data=sample out=want1(drop=_name_) suffix=_1 ;

     by emp;

     var amount;

     id date;

run;

proc transpose data=sample out=want2(drop=_name_) suffix=_2 ;

     by emp;

     var INCRE;

     id date;

run;

data want;

    merge want1 want2;

    by emp;

run;

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 85

Re: Converting rows into columns

I am getting output as

emp 10/13_1  11/13_1  12/13_1  10/13_2 11/13_2 12/13_2

1111 5000 5000 2000 1200 1200 1221

1112 3200 3200 3200 1000 1400 1200

1113 4200 4200 5200 1258 1500 1300

1114 5100 5100 7100 1258 1000 1425

1115 7200 7200 6200 1000 1000 1221

1116 6000 6000 2000 500 1255 1000

prefix _1 and _2 should not be there i want only month and year as heading of both data sets

Ask a Question
Discussion stats
  • 5 replies
  • 376 views
  • 0 likes
  • 4 in conversation