Help using Base SAS procedures

dummy records for transpose

Reply
Frequent Contributor
Posts: 115

dummy records for transpose

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;

Respected Advisor
Posts: 4,925

Re: dummy records for transpose

Posted in reply to sunilreddy

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

PG
Ask a Question
Discussion stats
  • 1 reply
  • 123 views
  • 0 likes
  • 2 in conversation