topic Re: Append a string to a macro variable for name of output data set. in SAS Programming

Append a string to a macro variable for name of output data set.

ncvol82 — Thu, 24 Oct 2019 21:11:02 GMT

The code below works fine. The output file is the same as the input with 'TBL_' added as a name prefix. However, when I try to append '_TBL' as a suffix, rather than prefix, the step fails thinking that the '_TBL' is part of the macro variable name.

%MACRO PREP_TBL;

%DO I = 1 %TO 3 ;
DATA TBL_&&TBL&i|' ;
SET &&TBL&i ;
WHERE DATE_ID GE 20160701 AND DATE_ID LE 20180630 ;
RUN;
%END ;

%PREP_TBL ;

%MEND;

Re: Append a string to a macro variable for name of output data set.

PaigeMiller — Thu, 24 Oct 2019 21:27:48 GMT

Show us the code that doesn't work.

Re: Append a string to a macro variable for name of output data set.

ncvol82 — Thu, 24 Oct 2019 21:39:10 GMT

%MACRO PREP_TBL;
34
35 %DO I = 1 %TO 1 ;
36 DATA &&TBL&i._TBL ;
37 SET &&TBL&i ;
38 WHERE DATE_ID GE 20160701 AND DATE_ID LE 20180630 ;
39 TRAN_DT = INPUT(PUT(DATE_ID,Z8.),YYMMDD8.) ;
40 TRAN_DYOFWK = WEEKDAY(TRAN_DT) ;
41 TRAN_REMDAYS = 7 - TRAN_DYOFWK ;
42 TRAN_WKEND = TRAN_DT + (TRAN_REMDAYS) ;
43 FORMAT TRAN_DT TRAN_WKEND MMDDYY10. ;
44 DROP TRAN_REMDAYS TRAN_DYOFWK ;
45 RUN;
46 %END ;
47
48 proc sort ;
49 by LOAN_ID TRAN_WKEND TRAN_DT ;
50 run;
51
52 %MEND;
53
54 %PREP_TBL;
MLOGIC(PREP_TBL): Beginning execution.
MLOGIC(PREP_TBL): %DO loop beginning; index variable I; start value is 1; stop value is 1; by value is 1.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
WARNING: Apparent symbolic reference TBL1_TBL not resolved.

Re: Append a string to a macro variable for name of output data set.

PaigeMiller — Thu, 24 Oct 2019 21:43:34 GMT

DATA &&TBL&i.._TBL ;

Use two dots to end a macro variable reference that begins with &&

Re: Append a string to a macro variable for name of output data set.

VDD — Fri, 25 Oct 2019 01:07:29 GMT

You may also need the double dots on this line

SET &&TBL..&i ;

Re: Append a string to a macro variable for name of output data set.

Kurt_Bremser — Fri, 25 Oct 2019 07:37:49 GMT

@ncvol82 wrote:

The code below works fine. The output file is the same as the input with 'TBL_' added as a name prefix. However, when I try to append '_TBL' as a suffix, rather than prefix, the step fails thinking that the '_TBL' is part of the macro variable name.

%MACRO PREP_TBL;

%DO I = 1 %TO 3 ;
DATA TBL_&&TBL&i|' ;
SET &&TBL&i ;
WHERE DATE_ID GE 20160701 AND DATE_ID LE 20180630 ;
RUN;
%END ;

%PREP_TBL ;

%MEND;

This code cannot work, as the pipe character and a single quote are not allowed in SAS names.

You also have a data design problem, as you seem to store dates as funny numbers and not as SAS date values.

Re: Append a string to a macro variable for name of output data set.

Tom — Fri, 25 Oct 2019 13:35:21 GMT

You can fix it with making sure you tell SAS where your macro variable reference ends.

But why bother. Just take the time to write out the steps and neither SAS nor you will get confused about what you are trying to do.

%macro prep_tbl;
%local i in out;
%do i = 1 %to 1 ;
  %let in=&&tbl&i;
  %let out=&in._tbl;

data &out;
  set &in;
  where date_id ge 20160701 and date_id le 20180630 ;
  tran_dt = input(put(date_id,z8.),yymmdd8.) ;
  tran_dyofwk = weekday(tran_dt) ;
  tran_remdays = 7 - tran_dyofwk ;
  tran_wkend = tran_dt + (tran_remdays) ;
  format tran_dt tran_wkend mmddyy10. ;
  drop tran_remdays tran_dyofwk ;
run;

%end ;

%mend prep_tbl;

Re: Append a string to a macro variable for name of output data set.

ncvol82 — Fri, 25 Oct 2019 13:48:37 GMT

The two dots worked. Thanks.