format creation and macros

[AJ_Brien]

Hello,

I'm learning to create formats for a bunch of datasets such that if the column value (ID in this case) when converted into that format exists, then I delete that observation.

For eg. My sample dataset:

ID	Indicator	col2	col3
330	Y	any random value	any random value
456	Y	any random value	any random value
356	N	any random value	any random value
689	Y	any random value	any random value

there are a bunch of other datasets, and i need to create formats for all of them, so I decided to create a macro for creating formats.

%macro fmt(var,ds);
data &var.;
set &ds.;
fmtname="$&var.";
type = 'c';
start = strip (id);
end = strip (id);
label = "YES";

keep = fmtname type start end label;
run;

proc format cntlin=&var.; 
run;

%mend fmt;
%fmt(abc_out, abc_in); /*bunch of more macro calls like this to create formats*/

this is how I plan to use those formats:
data final;
set....;
if put(id,$abc_out) = "YES" then delete;
/*bunch of more if conditions for other formats*/
run;

When I run the format macro, I get a bunch of errors, not sure how to solve those. Appreciate the help!

Logs:

MLOGIC(FMT): Beginning execution.
MLOGIC(FMT): Parameter VAR has value abc_out
MLOGIC(FMT): Parameter DS has value abc_in
SYMBOLGEN: Macro variable VAR resolves to abc_out
SYMBOLGEN: Macro variable DS resolves to abc_in
SYMBOLGEN: Macro variable VAR resolves to abc_out
388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ABC_OUT may be incomplete. When this step was stopped there were 0 observations and 4 variables.

[Reeza]

Do you have MPRINT on as well? If so, go to line 388 in the log - first error in your log and let us know which one that is...or post the whole log.

[AJ_Brien]

I turned mprint on and ran the code, strangely there is no line 388 in the log nor in the code. Here is the complete log listed:

1 The SAS System 15:24 Friday, October 25, 2019

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH=xx.egp';
6 %LET _CLIENTPROJECTPATHHOST='xx';
7 %LET _CLIENTPROJECTNAME='xx.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS94_EG/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to
"/saswork1/xx.com/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options mautosource sasautos= (
27 'xxxx')
28
29 nocenter source2 mlogic symbolgen mprint;
30 data dat1;
31 input id acc apps $;
32 cards;

NOTE: Compression was disabled for data set WORK.DAT1 because compression overhead would increase the size of the data set.
NOTE: The data set WORK.DAT1 has 6 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 422.46k
OS Memory 27300.00k
Timestamp 10/25/2019 10:58:16 PM
Step Count 116 Switch Count 2
Page Faults 0
Page Reclaims 50
Page Swaps 0
Voluntary Context Switches 25
Involuntary Context Switches 0
Block Input Operations 16
Block Output Operations 136

39 ;

40 run;
2 The SAS System 15:24 Friday, October 25, 2019

41
42
43 %macro fmt(var,ds);
44 data &var.;
45 set &ds.;
46 fmtname="$&var.";
47 type = 'c';
48 start = strip (rmg_id);
49 end = strip (rmg_id);
50 label = "YES";
51
52 keep = fmtname type start end label;
53 run;
54 proc format cntlin=&var.;
55 run;
56 %mend fmt;
57 %fmt(dat1_fmt, dat1);
MLOGIC(FMT): Beginning execution.
MLOGIC(FMT): Parameter VAR has value dat1_fmt
MLOGIC(FMT): Parameter DS has value dat1
SYMBOLGEN: Macro variable VAR resolves to dat1_fmt
MPRINT(FMT): data dat1_fmt;
SYMBOLGEN: Macro variable DS resolves to dat1
MPRINT(FMT): set dat1;
SYMBOLGEN: Macro variable VAR resolves to dat1_fmt
388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
MPRINT(FMT): fmtname="$dat1_fmt";
MPRINT(FMT): type = 'c';
MPRINT(FMT): start = strip (rmg_id);
MPRINT(FMT): end = strip (rmg_id);
MPRINT(FMT): label = "YES";
MPRINT(FMT): keep = fmtname type start end label;
MPRINT(FMT): run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
6700:69 6700:91
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DAT1_FMT may be incomplete. When this step was stopped there were 0 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 550.40k
OS Memory 27300.00k
Timestamp 10/25/2019 10:58:16 PM
Step Count 117 Switch Count 2
Page Faults 0
Page Reclaims 26
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264

3 The SAS System 15:24 Friday, October 25, 2019


SYMBOLGEN: Macro variable VAR resolves to dat1_fmt
MPRINT(FMT): proc format cntlin=dat1_fmt;
MPRINT(FMT): run;
NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 334.71k
OS Memory 27300.00k
Timestamp 10/25/2019 10:58:16 PM
Step Count 118 Switch Count 0
Page Faults 0
Page Reclaims 19
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 16

NOTE: There were 0 observations read from the data set WORK.DAT1_FMT.

MLOGIC(FMT): Ending execution.
58
59 GOPTIONS NOACCESSIBLE;
60 %LET _CLIENTTASKLABEL=;
61 %LET _CLIENTPROCESSFLOWNAME=;
62 %LET _CLIENTPROJECTPATH=;
63 %LET _CLIENTPROJECTPATHHOST=;
64 %LET _CLIENTPROJECTNAME=;
65 %LET _SASPROGRAMFILE=;
66 %LET _SASPROGRAMFILEHOST=;
67
68 ;*';*";*/;quit;run;
69 ODS _ALL_ CLOSE;
70
71
72 QUIT; RUN;
73

[SASKiwi]

I suspect you have a CARDS statement inside a SAS macro. This is not allowed in SAS so you must move that DATA step outside your macro or read your data in from an external file.

[AJ_Brien]

Thank you for your reply, this is the code I'm running:
data dat1;
input id acc apps $;
cards;
1 1 11
2 2 22
2 3 33
1 4 33
2 6 66
2 7 22
;
run;


%macro fmt(var,ds);
data &var.;
set &ds.;
fmtname="$&var.";
type = 'c';
start = strip (id);
end = strip (id);
label = "YES";

keep = fmtname type start end label;
run;
proc format cntlin=&var.;
run;
%mend fmt;
%fmt(dat1_fmt, dat1);

[SASKiwi]

So does the new version work? If not post your SAS log.

[Kurt_Bremser]

This is invalid syntax:

keep = fmtname type start end label;

The equals sign is invalid in a keep statement. Equal signs are necessary for the keep dataset option.

You made the mistake of not testing your SAS code before putting it into a macro. Rule #1 for macro development: start with working macro-free code.

[AJ_Brien]

Thank you for your suggestion. I ran the code without the macro and this is what I have as the code that works:

proc sort data=abc nodupkey;
by id;
run;

data abc_format;
set abc;

fmtname = '$state_frmt';
type = 'c';
start = strip(id);
end = strip(id);
label = 'yes';

keep fmtname type start end label;
run;


proc format cntlin= abc_format;
run;

Now when I try and add a macro, I get an error because I'm trying to create a fmtname that has a macro variable and I'm unable to figure out the right way of doing it. would appreciate your inputs 🙂

Below is the code with the macro:

%macro create_format(ds);
proc sort data=&ds. nodupkey;
by id;
run;

data &ds._format;
set &ds.;

fmtname = '$&ds._format';
type = 'c';
start = strip(id);
end = strip(id);
label = 'YES';

keep fmtname type start end label;
run;


proc format cntlin= &ds._format;
run;
%mend;
%create_format(abc);

the error that I get is:
ERROR: Format name '$&DS._FORMAT' is invalid. Observation ignored.

[Kurt_Bremser · Accepted solution]

Macro variables will not be resolved inside single quotes. Use double quotes, as in your initial post.

[AJ_Brien]

adding double quotes worked, thank you so much!