I have the following data and query
data have;
informat date date9. quantity 2.;
input part $ date quantity;
format date date9. quantity 2.;
datalines;
A 01jan2013 10
A 01JUN2013 5
A 12DEC2013 4
A 01FEB2014 6
A 15JUN2014 20
B 20FEB2013 5
B 15JAN2013 6
;
proc sql;
create table almostwant as
select part, year(date) as year, sum(quantity) as quantity
from have
group by part, year
;
quit;
I would like to output this data so that each year is created as a new variable rather then in a list format. So i would like to see the data look like below but i cant seem to get it to work. Any suggestions would be greatly appreciated.
Want:
Part 2013qty 2014qty
A 19 26
B 11 .
Complete the solution with Proc TRANSPOSE:
proc transpose data=almostwant out=want(drop=_name_) prefix=QTY;
by part;
id year;
var quantity;
quit;
Regards,
Complete the solution with Proc TRANSPOSE:
proc transpose data=almostwant out=want(drop=_name_) prefix=QTY;
by part;
id year;
var quantity;
quit;
Regards,
proc sql;
select part, max(_2013qty) as _2013qty,max(_2014qty) as _2014qty
from (
select part, ifn(year(date)=2013,sum(quantity),0) as _2013qty,
ifn(year(date)=2014, sum(quantity),0) as _2014qty
from have group by part,year(date)
) group by part;
quit;
Hi dsbihill,
First of all you cannot have SAS variable names starting with numbers. So your ‘Want:’ example shows labels and not variable names.
It is a standard proc transpose task:
data have;
informat date date9. quantity 2.;
input part $ date quantity;
format date date9. quantity 2.;
datalines;
A 01jan2013 10
A 01JUN2013 5
A 12DEC2013 4
A 01FEB2014 6
A 15JUN2014 20
B 20FEB2013 5
B 15JAN2013 6
;
proc sql;
create table almostwant as
select part, cats(put(year(date), best4.), 'qty') as year lenght=8, sum(quantity) as quantity
from have
group by part, year
order by 1, 2 ;
quit;
proc transpose data=almostwant
out=want(drop=quantity)
name=quantity;
by part;
id year;
idlabel year;
run;
data want;
set have;
year=year(date);
run;
proc tabulate data=want;
class part year;
var quantity;
table part,year=' '*quantity*sum=' '*f=8.0;
run;
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.