How do I use stored variable names when calling user-defined formats

[mmasek]

I'm using SAS version 9.4 and I want to call specific stored variables and formats based on a mapping of base year and final year, but it is not recognizing the user-defined format names.  I've provided a copy of my code below:

*Parameters passed to SAS from Excel;
%let b_year=2017;
%let p_year=2019;

*Input mapping grid by base year and final year;
data pd_rs_vars;
length b_yr p_yr $4 pd_rs_base pd_rs_fin $2 bmadj1 $11;
input b_yr $ p_yr $ pd_rs_base $ pd_rs_fin $ bmadj1 $;
datalines;
2015 2016 14 16 frtn_sxtn
2015 2017 14 17 fftn_svtn
2015 2018 14 18 fftn_nintn
2015 2019 14 18 fftn_nintn
2016 2017 16 17 sxtn_svtn
2016 2018 16 18 sxtn_nintn
2016 2019 16 18 sxtn_nintn
2017 2018 17 18 svtn_nintn
2017 2019 17 18 svtn_nintn
;
run;

*Retain only that row that corresponds with selected base and final year;
data pd_rs_vars_fin;
set pd_rs_vars (where=(b_yr="&b_year" and p_yr="&p_year"));
run;

*Assign values based on base year and final year as stored variables;
proc sql noprint;
select pd_rs_base into :pd_rs_base from pd_rs_vars_fin where (b_yr="&b_year" and p_yr="&p_year");
select pd_rs_fin into :pd_rs_fin from pd_rs_vars_fin where (b_yr="&b_year" and p_yr="&p_year");
select bmadj1 into :bmadj1 from pd_rs_vars_fin where (b_yr="&b_year" and p_yr="&p_year");
quit;
 
*Use stored variable names to assign values;
data mmrfinal;
set mmrlast;
key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
b_year=trim("&b_year");
pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
bm_rxhccadj=input(put(key_rxhcc,bmadj_&bmadj1..),16.);
pd_rs_proj_fin=pd_rs_&pd_rs_fin._fin;
run;

I am getting an error on the highlighted line in the last data step above. Here’s the log:

27507  *Use stored variable names to assign values;
27508  data mmrfinal;
27509  set mmrlast;
NOTE: SGIO processing active for file WKSP.MMR_MD.DATA.
NOTE: SGIO processing active for file WKSP.MMR_MD_BM_REMIX.DATA.
27510  key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
27511  b_year=trim("&b_year");
SYMBOLGEN:  Macro variable B_YEAR resolves to 2017
27512  pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
SYMBOLGEN:  Macro variable PD_RS_BASE resolves to 17
27513  bm_rxhccadj=input(put(key_rxhcc,bmadj_&bmadj1..),16.);
SYMBOLGEN:  Macro variable BMADJ1 resolves to svtn_nintn
NOTE: Line generated by the macro variable "BMADJ1".
1      bmadj_svtn_nintn
       ----------------
       85
       76
ERROR 85-322: Expecting a format name.

ERROR 76-322: Syntax error, statement will be ignored.

27514  pd_rs_proj_fin=pd_rs_&pd_rs_fin._fin;
SYMBOLGEN:  Macro variable PD_RS_FIN resolves to 18
27515  run;

NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: View WORK.MMRLAST.VIEW used (Total process time):
      real time           0.26 seconds
      cpu time            0.06 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MMRFINAL may be incomplete.  When this step was stopped there were 0 observations
         and 31 variables.
WARNING: Data set WORK.MMRFINAL was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.27 seconds
      cpu time            0.09 seconds

I’ve tried adding another period to the line in question to get SAS to recognize the user-defined format name, but I get the same error. Does anyone else know how I can get this to work?

Thanks in advance!

[r_behata]

If you are trying to use a character format at run time use the putc function instead.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212562.htm

[mmasek]

Thanks for the response.  I've modified the last data step as follows:

*Use stored variable names to assign values;
data mmrfinal;
set mmrlast;
key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
b_year=trim("&b_year");
pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
bm_rxhccadj=input(putc(key_rxhcc,bmadj_&bmadj1..),16.);
pd_rs_proj_fin=pd_rs_&pd_rs_fin._fin;
run;

And now I'm getting the following error in my log:

27570  *Use stored variable names to assign values;
27571  data mmrfinal;
27572  set mmrlast;
NOTE: SGIO processing active for file WKSP.MMR_MD.DATA.
NOTE: SGIO processing active for file WKSP.MMR_MD_BM_REMIX.DATA.
SYMBOLGEN:  Macro variable B_YEAR resolves to 2017
SYMBOLGEN:  Macro variable PD_RS_BASE resolves to 17
27573  key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
SYMBOLGEN:  Macro variable BMADJ1 resolves to svtn_nintn
27574  b_year=trim("&b_year");
27575  pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
SYMBOLGEN:  Macro variable PD_RS_FIN resolves to 18
27576  bm_rxhccadj=input(putc(key_rxhcc,bmadj_&bmadj1..),16.);
                                                      -
                                                      22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =,
              >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

27577  pd_rs_proj_fin=pd_rs_&pd_rs_fin._fin;
27578  run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      1:1
NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: View WORK.MMRLAST.VIEW used (Total process time):
      real time           0.53 seconds
      cpu time            0.04 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MMRFINAL may be incomplete.  When this step was stopped there were 0 observations
         and 32 variables.
WARNING: Data set WORK.MMRFINAL was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.54 seconds
      cpu time            0.04 seconds

I need to review the information in the link provided closer, but have to jump over to a few client calls now.  If you have any other ideas or advice, it would be much appreciated.

Thanks!

[data_null__]

isn't the error related to this macro variable which is not resolved. 

_&bmadj1

27575  pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
SYMBOLGEN:  Macro variable PD_RS_FIN resolves to 18
27576  bm_rxhccadj=input(putc(key_rxhcc,bmadj_&bmadj1..),16.);
                                                      -
                                                      22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =,
              >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

[mmasek]

The variable is being resolved, but it seems like it is not recognizing the result as a user-defined format.

[Reeza]

You can't just replace the function without changing anything else, it has to meet the functions requirements, Which means no periods and put it as text. Otherwise SAS will interpret it as a variable. A general rule is make sure you code works without macro variables and then add in the macro variable. Or at least use that as a debugging step.

[Reeza]

Try PUTN/PUTC which allows you pass the format as a variable name or character string.

[Tom]

So you tried to run

data mmrfinal;
  set mmrlast;
  key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
  b_year=trim("2017");
  pd_rs_base_fin=pd_rs_17_fin;
  bm_rxhccadj=input(put(key_rxhcc,bmadj_svtn_nintn.),16.);
  pd_rs_proj_fin=pd_rs_18_fin;
run;

Does the format bmadj_svtn_nintn exist?  Or since you just used KEY_RXHCC as a character variable earlier in the step really you want to know if the format $bmadj_svtn_nintn exists.

Try running the code above and see what error message you get. If it is different then perhaps you are having a timing issue and you just need to add in a %UNQUOTE() macro function call so that SAS will treat your generated string as one token when it is parsing the generated SAS code.

bm_rxhccadj=input(put(key_rxhcc,%unquote($bmadj_&bmadj1..)),16.);

[mmasek]

Yes, thank you.  I've run the code below as part of my troubleshooting:

*Use stored variable names to assign values;
data mmrfinal;
set mmrlast;
key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
b_year=trim("2017");
pd_rs_base_fin=pd_rs_17_fin;
bm_rxhccadj=input(put(key_rxhcc,bmadj_svtn_nintn.),16.);
pd_rs_proj_fin=pd_rs_18_fin;
run;

This runs fine.  This is actually a subset of the original code that I am trying to replace.  My log is below:

27700  *Use stored variable names to assign values;
27701  data mmrfinal;
27702  set mmrlast;
NOTE: SGIO processing active for file WKSP.MMR_MD.DATA.
NOTE: SGIO processing active for file WKSP.MMR_MD_BM_REMIX.DATA.
27703  key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
27704  b_year=trim("2017");
27705  pd_rs_base_fin=pd_rs_17_fin;
27706  bm_rxhccadj=input(put(key_rxhcc,bmadj_svtn_nintn.),16.);
27707  pd_rs_proj_fin=pd_rs_18_fin;
27708  run;

NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: SGIO processing active for file WORK.MMRFINAL.DATA.
NOTE: There were 759940 observations read from the data set WKSP.MMR_MD_BM_REMIX.
      WHERE ult_ptd_mm;
NOTE: There were 661784 observations read from the data set WKSP.MMR_MD.
      WHERE (ult_planid not = 'xxx') and ult_ptd_mm;
NOTE: View WORK.MMRLAST.VIEW used (Total process time):
      real time           9.02 seconds
      cpu time            1.90 seconds

NOTE: There were 67949 observations read from the data set WORK.MMRLAST_CLIENT.
NOTE: There were 64996 observations read from the data set WORK.MMRLAST_BM.
NOTE: There were 132945 observations read from the data set WORK.MMRLAST.
NOTE: The data set WORK.MMRFINAL has 132945 observations and 31 variables.
NOTE: Compressing data set WORK.MMRFINAL decreased size by 42.06 percent.
      Compressed is 547 pages; un-compressed would require 944 pages.
NOTE: DATA statement used (Total process time):
      real time           9.06 seconds
      cpu time            1.92 seconds

I tried adding a %UNQUOTE() macro function call as suggested, but still received similar errors.  It seems like SAS is not recognizing the string [bmadj_&bmadj1..] as a format name, like it does for [bmadj_svtn_nintn.].

[Tom]

It still looks like you should have character format instead of a numeric format. 

What happens if you switch to using PUTC() instead?  Syntax would be :

bm_rxhccadj=input(putc(key_rxhcc,"$bmadj_svtn_nintn."),16.);

Try with and without the $.

Are you sure that the format is defined and SAS knows where to find it?

Actually fix your SQL query.

Why did you have you use the TRIM() funciton when assigning the year?  Your SQL query probably added trailing spaces into your macro variable.  Include the TRIMMED keyword into INTO clause in PROC SQL to avoid adding the trailing spaces.

select varname into :mvarname trimmed from tablename;

[mmasek · Accepted solution]

Thanks!  You were correct that I did not have the correct syntax when I tried using the PUTC() function previously.  The following code works:

*Use stored variable names to assign values;
data mmrfinal;
set mmrlast;
key_rxhcc=trim(ult_liscat)||put(AG_ID,z2.);
b_year=trim("&b_year");
pd_rs_base_fin=pd_rs_&pd_rs_base._fin;
bm_rxhccadj=input(putc(key_rxhcc,"bmadj_&bmadj1."),16.);
pd_rs_proj_fin=pd_rs_&pd_rs_fin._fin;
run;