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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.