I want to create transpose data set on previous year and current year quarters. i dont have always 24 months data in dataset to create all quarters in transpose. so i want to create dummy records to use in transpose.
Month REQ_F_P num1
01MAY2011 2011Q2 1
01SEP2011 2011Q3 3
01OCT2012 2012Q4 4
In above example, dont have all quarters in previous year and current year to transpose. Want to transpose the data on below different combination of BY variables for REQ_F_P column
PROC TRANSPOSE Data=test;
BY PRODUCT_LEVEL2_ID REGION_ID CUSTOMER_ATTRIB_4_CD ;
id REQ_F_P;
run;
Assuming PRODUCT_LEVEL2_ID is numeric :
data have;
informat month date9.;
format month yymm6.;
PRODUCT_LEVEL2_ID =123;
REGION_ID = 234;
CUSTOMER_ATTRIB_4_CD = 345;
input Month REQ_F_P $ num1;
datalines;
01MAY2011 2011Q2 1
01SEP2011 2011Q3 3
01OCT2012 2012Q4 4
;
data test;
retain yMin yMax;
set have end=last;
yMin = min(yMin, year(month));
yMax = max(yMax, year(month));
output;
if last then do;
PRODUCT_LEVEL2_ID = -999999999999;
do y = yMin to Ymax;
do m = 1 to 4;
REQ_F_P = cats(y, "Q",m);
output;
end;
end;
end;
drop yMin yMax y m;
run;
proc sort data=test; by PRODUCT_LEVEL2_ID REGION_ID CUSTOMER_ATTRIB_4_CD REQ_F_P; run;
PROC TRANSPOSE Data=test out=testTR(where=(PRODUCT_LEVEL2_ID>-999999999999));
BY PRODUCT_LEVEL2_ID REGION_ID CUSTOMER_ATTRIB_4_CD ;
id REQ_F_P;
var num1;
run;
PG
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.