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!
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;
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
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!
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, ^=, |, ||, ~=.
The variable is being resolved, but it seems like it is not recognizing the result as a user-defined format.
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.);
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.].
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: