BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

I find if very useful to separate macro variable manipulation from code generation.

If your case you should create two macro variables to sort the user selections into.

%let byvars=;

%let vars=;

%do i=1 %go &pmtselectcols_count ;

  %if %qsubstr(&&pmtselectcols&i,1,4) = CALC %then %let byvars=&byvars &&pmtselectcols&i;

  %else %let vars=&vars &&pmtselectcols&i;

%end;

...

  by cycle_dt &byvars t_forecast_period_dt;

  var &vars ;

...

This also helps to see that if the user ONLY select CALC_... variables an invalid VAR statement will be generated.

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

Hi:

  Some comments about this question have already been posted in the ODS Forum:

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

  If you are running this PROC TRANSPOSE as part of a stored process, then you need to post all of your code and/or your log. Are you sure that your macro variables have been declared as GLOBAL macro variables? You say you have 1 prompt with multiple values (which are variable names). In this case, your prompt definition will be important to understand (did you allow single values, did you allow multiple values)?

  How do you know that your %DO loop is not working correctly? Are you getting macro logic errors, macro compile errors? Or, are you getting PROC TRANSPOSE errors? Have you turned on MLOGIC, MPRINT and SYMBOLGEN options for debugging purposes? Do you know for sure that &PMTSELECTCOLS_COUNT is being populated correctly?

  You said you had tried a few things. The first thing to try is a working SAS program. When you use your hard-coded PROC TRANSPOSE, do you get the layout your want and the correct results? If this program generates the expected layout, then that is good and you can proceed to "macro-ize" the program. However, if this hard-coded program does not produce the desired layout or transposed data, then there is nothing to be gained by macro-izing a program that doesn't give you what you want.

   The next thing to try is inserting macro variables into your working code. Macro variables will NEVER resolve if enclosed in singe quotes and a quoted value for a BY variable or a VAR variable is not appropriate for PROC TRANSPOSE.

  You might be better served by opening a track with Tech Support. They could look at ALL of your code, and if this is a process code, they could look at the metadata definitions for the prompts. If this is a prompt you are building in an EG project, then your code doesn't look like EG-generated code to me. Tech Support could look at your prompt definitions. They could look at your data and look at your entire log and help you figure out the best resolution.

  The reference that you use  &&PMTSELECTCOLS&I (without any quotes) should be the correct reference, as illustrated in the simplified code below. So, that indicates to me you have some other issue with your macro variable values or your PROC TRANSPOSE. That's why my recommendation is for you to work with Tech Support. Your SAS log, with the debugging options turned on, should tell you right away whether your macro variables were being resolved. That information will be essential to anyone helping with this question.

cynthia

** populate some macro variables with values to test usage;

%let pmtselectcols_count=4;
%let PMTSELECTCOLS1 = name;
%let PMTSELECTCOLS2 = age;
%let PMTSELECTCOLS3 = height;
%let PMTSELECTCOLS4 = weight;
 

** use a simple proc print with a %DO loop to write a VAR statement;
%macro print_data;
  
proc print data=sashelp.class;
  var %do i=1 %to &pmtselectcols_count;
       &&PMTSELECTCOLS&I
      %end;
      ;
  run;
 
%mend print_data;

** now turn on debugging options and run the macro program;  
ods listing;
options mlogic mprint symbolgen;
%print_data;

options nomlogic nomprint nosymbolgen;

SAS Log using SYMBOLGEN (highlighting mine):

MPRINT(PRINT_DATA): proc print data=sashelp.class;

SYMBOLGEN: Macro variable PMTSELECTCOLS_COUNT resolves to 4

MLOGIC(PRINT_DATA): %DO loop beginning; index variable I; start value is 1; stop value is 4; by value is 1.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable I resolves to 1

SYMBOLGEN: Macro variable PMTSELECTCOLS1 resolves to name

MLOGIC(PRINT_DATA): %DO loop index variable I is now 2; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable I resolves to 2

SYMBOLGEN: Macro variable PMTSELECTCOLS2 resolves to age

MLOGIC(PRINT_DATA): %DO loop index variable I is now 3; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable I resolves to 3

SYMBOLGEN: Macro variable PMTSELECTCOLS3 resolves to height

MLOGIC(PRINT_DATA): %DO loop index variable I is now 4; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable I resolves to 4

SYMBOLGEN: Macro variable PMTSELECTCOLS4 resolves to weight

MLOGIC(PRINT_DATA): %DO loop index variable I is now 5; loop will not iterate again.

MPRINT(PRINT_DATA): var name age height weight ;

MPRINT(PRINT_DATA): run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

sunilreddy
Fluorite | Level 6

Hi,

Thanks for your response.

I am getting error when i am not selecting any VAR list from promt. is thr any scenaro to use empty list in VAR STMT.  if i am not selecting any promt values for VAR stmt.

In below code, incase if am not selecting any values iin VAR stmt.

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;


Tom
Super User Tom
Super User

I find if very useful to separate macro variable manipulation from code generation.

If your case you should create two macro variables to sort the user selections into.

%let byvars=;

%let vars=;

%do i=1 %go &pmtselectcols_count ;

  %if %qsubstr(&&pmtselectcols&i,1,4) = CALC %then %let byvars=&byvars &&pmtselectcols&i;

  %else %let vars=&vars &&pmtselectcols&i;

%end;

...

  by cycle_dt &byvars t_forecast_period_dt;

  var &vars ;

...

This also helps to see that if the user ONLY select CALC_... variables an invalid VAR statement will be generated.

Cynthia_sas
SAS Super FREQ

Hi:

  Originally you said you were getting the message: "PMTSELECTCOLS1 is not resolved" - -so not you are reporting a different issue. Did you fix the "not resolved" issue?

  If you define a prompt with multiple selections, then your code would have to deal with setting a default when no selections were made. You have not said whether this is a stored process or prompts in Enterprise Guide. If this is a stored process, then the server handling the stored process will create certain "utility" macro variables IF a selection is made. For example, let's say you define a prompt called COUNTRY in your stored process and you allow country to have multiple selections.

  Let's then say that the user selects 3 values for COUNTRY. What will happen is that the server will create 6 macro variables:

COUNTRY_COUNT=3

COUNTRY0=3

COUNTRY1=Denmark

COUNTRY2=France

COUNTRY3=Sweden

COUNTRY=Denmark

In this case, with 3 values selected, macro variables &COUNTRY_COUNT and &COUNTRY0 are both created and hold a value of 3. &COUNTRY1, &COUNTRY2 and &COUNTRY3 hold the values the user selected. &COUNTRY (which is the "base" macro variable) will hold one of the values, usually the first user selection.

  But what if the user makes only 1 selection? Then in this case, the server creates THESE macro variables:

COUNTRY_COUNT=1

COUNTRY=Denmark

as you can see, for 1 selection, no numbered macro variables are created. In addition &COUNTRY0 is not created. Your program logic would have to account for this situation. Generally, you would use logic similar to this, if you need to process values in a %DO loop:

%if &country_count = 1 %then %let &country1 = &country;

  Then what happens if the user makes NO selections, no selections at all. If you define your prompt correctly (click the switch so that they MUST make a selection), then you will not have to deal with this situation. I feel that it is a best practice to ALWAYS make the user choose something for a prompt instead of allowing them to skip over a prompt. If you are going to let them skip over a prompt and not require a value, then you need to set a default for the prompt - -either in the prompting interface or in your macro program code.

  If the user makes NO selections -- because you didn't require a selection -- then the server can't really do much for you. The server would only return:

COUNTRY_COUNT=0

  The server would NOT create numbered macro variables for a no selection scenario. And, the BASE macro (in this case, &COUNTRY) variable would not get created either. If you have a %GLOBAL statement in your code, then at least the base macro variable would be created with a NULL value in the global symbol table.

  For the no selection scenario, your macro code could test for:

%if &country_count = 0 %then %do;

     %let &country_count1 = default_var_name_or_list ;

     %let &country_count = 1;

%end;

  I still think you should open a track with Tech Support on this issue. I'm still not convinced your TRANSPOSE is working correctly and seeing ALL of your code and ALL of your LOG would be important to getting the right answer.

cynthia

Tom
Super User Tom
Super User

Thanks for the explaination of the how selection lists are converted to macro variables.

Wow.....I hope SAS is looking into how to make this more friendly for the stored process programmers.

As I interpret this we have to handle three situations:

count=0

count=1

count>1

In each of them the program will receive a different set of macro variables.

Cynthia_sas
SAS Super FREQ

Hi, Tom:

  This is friendlier than it used to be under SAS/IntrNet with multiple selections. The original numbered macro variables and &macvar0 for multiple selections and how they were handled was for compatibility with IntrNet Dispatcher programs that were being converted to stored processes. Those folks already had macro logic in place to account for 0, 1 or <1 selections. And their macro logic was MORE complicated because &COUNTRY_COUNT=0 would not have been created in the "old" IntrNet days -- you had to test for the presence or absence of &macvar0. So, now, with stored processes you ALWAYS have an _COUNT macro variable to test as a stored process developer.

  This is why I made a distinction between stored process prompt handling and EG prompt handling. I believe that EG handles multiple selection prompts slightly differently when you're only at the project level versus the stored process level. At the stored process level, the programmer would have to test the value of &COUNTRY_COUNT, for example - -IMO it should always be the programmer's responsibility to handle those 3 scenarios in whichever way is appropriate for the stored process. Almost any "automated" decision that SAS would make for the no selection scenario would be wrong for someone.

  In fact, that's why I said it was a best practice to always make the user select -something- because then you only have 2 scenarios to test -- when there was 1 selection (&macvar_count=1) and when there was > 1 selection (&macvar_count > 1).

cynthia

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1735 views
  • 7 likes
  • 3 in conversation