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
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.
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;
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.