topic Re: Converting rows into columns in SAS Programming

Converting rows into columns

santhosh — Thu, 14 Nov 2013 04:33:17 GMT

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

Re: Converting rows into columns

Patrick — Thu, 14 Nov 2013 05:03:29 GMT

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.

Re: Converting rows into columns

Xianhua_zeng — Thu, 14 Nov 2013 05:25:08 GMT

proc sort data=sample out=int;

by emp;

run;

proc transpose data=int out=want(drop=_:) prefix=sal_ ;

by emp;

var sal;

id date;

run;

Re: Converting rows into columns

santhosh — Mon, 18 Nov 2013 13:09:55 GMT

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

Re: Converting rows into columns

Jagadishkatam — Sat, 23 Nov 2013 07:21:58 GMT

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

Re: Converting rows into columns

santhosh — Mon, 25 Nov 2013 11:47:25 GMT

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