dummy records for transpose

sunilreddy — Sun, 02 Dec 2012 23:10:10 GMT

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;

Re: dummy records for transpose

PGStats — Mon, 03 Dec 2012 01:35:16 GMT

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;

topic dummy records for transpose in SAS Procedures

dummy records for transpose

Re: dummy records for transpose