BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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;

when i choose only one value, i am getting macro variable PMTSELECTCOLS1 is not resolved. i tried with many ways (double quotes "&&PMTSELECTCOLS&I", single quotes '&&PMTSELECTCOLS&') to resolve thIS macro variable. 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;

3 REPLIES 3
Tom
Super User Tom
Super User

It looks to me like you have mixed apples and oranges into your selection list.

Your generated code wants to use the variables that start with CALC_ in your BY statement and variables that start with ADJ_ are listed in the VAR statement. Are these supposed to be matched pairs? Or are the list of BY variables and the list of analysis variables linked?

Cynthia_sas
SAS Super FREQ

Hi:

  Some comments about this question have already been posted in the ODS and the Enterprise Guide forums:

https://communities.sas.com/thread/34168?tstart=0
https://communities.sas.com/thread/34177?tstart=0

  In my post in the ODS forum, I asked about whether the OP wanted to have apparent "quantity" variables in the BY statement and the VAR statement for PROC TRANSPOSE. That question was not answered.

  Also, I think that understanding more about the data and seeing the SAS log (with SYMBOLGEN and MPRINT turned on) would be very useful.

cynthia

kuridisanjeev
Quartz | Level 8

Hey sunil..

why you are posting this same question in several forms for many time..

i think you got the answer from cynthia. i am not sure for what answer you are looking for..

Sanjeev.K

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 929 views
  • 0 likes
  • 4 in conversation