report logic issue

[sunilreddy]

Hi,

Prompt variable: PMTSELECTCOLS with below multiple static list values

CALC_GLOBAL_KEY_QTY
CALC_REGION_KEY_QTY
CALC_CUSTOMER_KEY_QTY
ADJ_GLOBAL_KEY_QTY
ADJ_REGION_KEY_QTY
ADJ_CUSTOMER_KEY_QTY
SELECTED_KEY_QTY

Currently below transpose is hardcoded with all columns eventhough if we are selecting few input values from above prompt.
but we need to give dynamic variables in BY and VAR stmt based on selected input prompt values

PROC TRANSPOSE DATA=temp_report_s
OUT=temp_report_transposed
PREFIX=Column
NAME=Source
LABEL=Label
;
BY CYCLE_DT  CALC_GLOBAL_KEY_QTY CALC_REGION_KEY_QTY CALC_CUSTOMER_KEY_QTY T_FORECAST_PERIOD_DT;
VAR ADJ_GLOBAL_KEY_QTY ADJ_REGION_KEY_QTY ADJ_CUSTOMER_KEY_QTY SELECTED_KEY_QTY
;
RUN;

proc print label data=report_transposed noobs width=full ;

run;

But i am not getting the expected layout with below code. Could you please help me to get the correct report layout

%macro test1;
PROC TRANSPOSE DATA=ddf_cont.temp_report_s
OUT=report_transposed
PREFIX=Column
NAME=Source
LABEL=Label
;
BY CYCLE_DT

       %do i=1 %to &PMTSELECTCOLS_COUNT
          %IF %SUBSTR(&&PMTSELECTCOLS&I,1,4) EQ "CALC" %THEN
              &&PMTSELECTCOLS&I;
          %END; T_FORECAST_PERIOD_DT;

VAR %do i=1 %to &PMTSELECTCOLS_COUNT
         %IF %SUBSTR(&&PMTSELECTCOLS&I,1,4) NE "CALC" %THEN
            &&PMTSELECTCOLS&I;
         %END;

;
RUN;

proc print label data=report_transposed noobs width=full ;

run;

%mend;

%test1;

[Cynthia_sas]

Hi:

  I don't exactly see how this is a "report" issue. It looks like most of the work happens in the PROC TRANSPOSE and in the Macro code. The PROC PRINT step is fairly trivial. PROC PRINT can only display what is created by PROC TRANSPOSE. So, I'm not sure that this is the right forum for your question. It seems to me either the SAS Procedures forum or the SAS Macro forum would be more appropriate.

  In any case, without seeing some sample data or understanding what you mean when you say that you are "not getting the expected layout" -- the forum participants have no idea what the data looks like, what the (wrong) output from PROC PRINT looks like or what your expected layout is.

  I also question some of the variables that you are using in the BY statement. Without any description or picture of your data, it is hard to understand why apparent "quantity" variables like: CALC_GLOBAL_KEY_QTY CALC_REGION_KEY_QTY would be in the BY statement, but other apparent "quantity" variables are in the VAR statement like: ADJ_GLOBAL_KEY_QTY and ADJ_REGION_KEY_QTY -- that doesn't make sense to me.

  But, as I said, without some idea of what your data looks like, it is hard to visualize what you're getting and what you expect. The PROC TRANSPOSE documentation is quite thorough and has good examples of using BY and VAR.

  Take a look at this PROC TRANSPOSE example. I have sorted by REGION and INVENTORY for the first TRANSPOSE. INVENTORY is a numeric variable that is not necessarily unique. It is not logical to put INVENTORY in a BY statement, although PROC TRANSPOSE will not complain. If you compare the results of that output with the output from the second TRANSPOSE (using  categorical variables like REGION, SUBSIDIARY and PRODUCT), I think the difference will be obvious.

  Cynthia

proc sort data=sashelp.shoes out=shoes;

by region inventory;

where region contains 'Europe';

run;

proc transpose data=shoes out=bad_by_var;

by region inventory;

var sales;

run; 

ods listing;

proc print data=bad_by_var;

  title 'Inappropriate BY Var';

run;

proc sort data=shoes out=newshoes;

by region subsidiary product;

run;

proc transpose data=newshoes out=good_by_var;

by region subsidiary product;

var sales inventory;

run;

proc print data=good_by_var;

  title 'Better BY Var';

run;