topic Re: EG and Macros in SAS Programming

EG and Macros

deleted_user — Wed, 09 Dec 2009 16:06:11 GMT

I can use EG to import several files manually, but when I try to use a macro to do the same thing I get an error that the file is not found. Please see the examples below. Example 1 is the EG summitted code and the second is my code followed by the error. Any help would be greatly appreciated.

Ex 1

/* -------------------------------------------------------------------
Code generated by SAS Task

Generated on: Wednesday, December 09, 2009 at 11:02:46 AM
By task: Import Data

Input Data: #LN00011
Server: D1SAS
------------------------------------------------------------------- */

%_eg_conditional_dropds(WORK.IMPW);
%MACRO _EG_ImportData;

%LET IsMVS=FALSE;
%IF %TRIM(&SYSSCP) = OS %THEN %DO;
%IF %sysfunc(getoption(filesystem))=MVS %THEN %DO;
%LET IsMVS=TRUE;
%END;
%END;
DATA WORK.IMPW;
/* Enterprise Guide creates this temporary asterisk delimited text file from the original data source */
INFILE #LN00011
DELIMITER='*'
MISSOVER
DSD
%IF &IsMVS=FALSE %THEN %DO;
LRECL=32767
%END;
FIRSTOBS=2
;
LENGTH
id 8
MEDICAID_NO $ 14
EFFECTIVE_DATE 8
MEMBER_LAST_NAME $ 18
MEMBER_FIRST_NAME $ 19
MEMBER_DOB 8
SEQ_MEMB_ID 8
INSERT_DATETIME 8
SUBSCRIBER_ID $ 15
PLAN_CODE $ 11
MEMBER_SEX $ 12
PS_COVERAGE_LOB $ 17
PS_COUNT_THE_MEMBER 8
PS_EXPECTED_AMOUNT 8
PS_REMIT_AMOUNT 8
PRC_PREMIUM_REPORT_CODE $ 25
_TYPE_ $ 8
_FREQ_ 8
cnt_N 8
;
INFORMAT
EFFECTIVE_DATE ANYDTDTM22.
MEMBER_DOB ANYDTDTM22.
INSERT_DATETIME ANYDTDTM22.
;
FORMAT
EFFECTIVE_DATE DATEAMPM22.0
MEMBER_DOB DATEAMPM22.0
INSERT_DATETIME DATEAMPM22.0
;
INPUT
id
MEDICAID_NO $
EFFECTIVE_DATE
MEMBER_LAST_NAME $
MEMBER_FIRST_NAME $
MEMBER_DOB
SEQ_MEMB_ID
INSERT_DATETIME
SUBSCRIBER_ID $
PLAN_CODE $
MEMBER_SEX $
PS_COVERAGE_LOB $
PS_COUNT_THE_MEMBER
PS_EXPECTED_AMOUNT
PS_REMIT_AMOUNT
PRC_PREMIUM_REPORT_CODE $
_TYPE_ $
_FREQ_
cnt_N
;
LABEL
id = "id"
MEDICAID_NO = "MEDICAID_NO"
EFFECTIVE_DATE = "EFFECTIVE_DATE"
MEMBER_LAST_NAME = "MEMBER_LAST_NAME"
MEMBER_FIRST_NAME = "MEMBER_FIRST_NAME"
MEMBER_DOB = "MEMBER_DOB"
SEQ_MEMB_ID = "SEQ_MEMB_ID"
INSERT_DATETIME = "INSERT_DATETIME"
SUBSCRIBER_ID = "SUBSCRIBER_ID"
PLAN_CODE = "PLAN_CODE"
MEMBER_SEX = "MEMBER_SEX"
PS_COVERAGE_LOB = "PS_COVERAGE_LOB"
PS_COUNT_THE_MEMBER = "PS_COUNT_THE_MEMBER"
PS_EXPECTED_AMOUNT = "PS_EXPECTED_AMOUNT"
PS_REMIT_AMOUNT = "PS_REMIT_AMOUNT"
PRC_PREMIUM_REPORT_CODE = "PRC_PREMIUM_REPORT_CODE"
_TYPE_ = "_TYPE_"
_FREQ_ = "_FREQ_"
cnt_N = "cnt_N"
;
RUN;
%MEND;
%_EG_ImportData
/* -------------------------------------------------------------------
End of task code.
------------------------------------------------------------------- */
RUN; QUIT;
TITLE; FOOTNOTE;
RUN;

************************************************************************************************

EX 2

filename in1 "/data/dept/finance/Dups/MONTH END ALL FILES/MONTH_END_0607_GA.csv";

%MACRO _EG_ImportData(filein,yr);

DATA WORK.MONTH_END_&yr;
/* Enterprise Guide creates this temporary asterisk delimited text file from the original data source */
INFILE &filein
DELIMITER='*'
MISSOVER
DSD
LRECL=32767
FIRSTOBS=2
;
LENGTH
id 8
MEDICAID_NO $ 14
EFFECTIVE_DATE 8
MEMBER_LAST_NAME $ 18
MEMBER_FIRST_NAME $ 19
MEMBER_DOB 8
SEQ_MEMB_ID 8
INSERT_DATETIME 8
SUBSCRIBER_ID $ 15
PLAN_CODE $ 11
MEMBER_SEX $ 12
PS_COVERAGE_LOB $ 17
PS_COUNT_THE_MEMBER 8
PS_EXPECTED_AMOUNT 8
PS_REMIT_AMOUNT 8
PRC_PREMIUM_REPORT_CODE $ 25
_TYPE_ $ 8
_FREQ_ 8
cnt_N 8
;
INFORMAT
EFFECTIVE_DATE ANYDTDTM22.
MEMBER_DOB ANYDTDTM22.
INSERT_DATETIME ANYDTDTM22.
;
FORMAT
EFFECTIVE_DATE DATEAMPM22.0
MEMBER_DOB DATEAMPM22.0
INSERT_DATETIME DATEAMPM22.0
;
INPUT
id
MEDICAID_NO $
EFFECTIVE_DATE
MEMBER_LAST_NAME $
MEMBER_FIRST_NAME $
MEMBER_DOB
SEQ_MEMB_ID
INSERT_DATETIME
SUBSCRIBER_ID $
PLAN_CODE $
MEMBER_SEX $
PS_COVERAGE_LOB $
PS_COUNT_THE_MEMBER
PS_EXPECTED_AMOUNT
PS_REMIT_AMOUNT
PRC_PREMIUM_REPORT_CODE $
_TYPE_ $
_FREQ_
cnt_N
;
LABEL
id = "id"
MEDICAID_NO = "MEDICAID_NO"
EFFECTIVE_DATE = "EFFECTIVE_DATE"
MEMBER_LAST_NAME = "MEMBER_LAST_NAME"
MEMBER_FIRST_NAME = "MEMBER_FIRST_NAME"
MEMBER_DOB = "MEMBER_DOB"
SEQ_MEMB_ID = "SEQ_MEMB_ID"
INSERT_DATETIME = "INSERT_DATETIME"
SUBSCRIBER_ID = "SUBSCRIBER_ID"
PLAN_CODE = "PLAN_CODE"
MEMBER_SEX = "MEMBER_SEX"
PS_COVERAGE_LOB = "PS_COVERAGE_LOB"
PS_COUNT_THE_MEMBER = "PS_COUNT_THE_MEMBER"
PS_EXPECTED_AMOUNT = "PS_EXPECTED_AMOUNT"
PS_REMIT_AMOUNT = "PS_REMIT_AMOUNT"
PRC_PREMIUM_REPORT_CODE = "PRC_PREMIUM_REPORT_CODE"
_TYPE_ = "_TYPE_"
_FREQ_ = "_FREQ_"
cnt_N = "cnt_N"
;
RUN;
%MEND;
%_EG_ImportData(in1,0607);

Error Message:

ERROR: Physical file does not exist, /data/dept/finance/Dups/MONTH END ALL
FILES/MONTH_END_0607_GA.csv.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MONTH_END_0607 may be incomplete. When this step was stopped there
were 0 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

Re: EG and Macros

SASPhile — Wed, 09 Dec 2009 16:22:36 GMT

In your code where is macro filein defined?

Re: EG and Macros

sbb — Wed, 09 Dec 2009 16:31:40 GMT

Suggest you run your code with the OPTIONS statement below and debug the SAS log for a problem source - then if you can't find it, re-post a reply with the SAS-generated log output for best results here.

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC MPRINT;

Scott Barry
SBBWorks, Inc.

Re: EG and Macros

Flip — Wed, 09 Dec 2009 16:32:24 GMT

Your 2 examples are using different files. Have you tried hard coding the second filename in the infile statement to make sure you cang actually read that file? It looks to me like the error may be in the file not being accessible, rather than a macro problem.

As always, make sure your code will run outside of the macro first.

Re: EG and Macros

deleted_user — Wed, 09 Dec 2009 18:37:15 GMT

No they are the same file. I was using the import tool in eg that creates a temp file starts with the # sysmbol.

Re: EG and Macros

deleted_user — Wed, 09 Dec 2009 18:38:30 GMT

The file IS there and can be imported using the EG tool but I can't seem to do it using the macros.

See the log:
1 The SAS System 13:34 Wednesday, December 9, 2009

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code1);
4 %LET _EGTASKLABEL=%NRBQUOTE(Code1);
5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(C:\Documents and Settings\DRAbbott\My
5 ! Documents\State_Dup_Recode.egp);
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series
of devices. Graph defaults for these drivers may be different from other SAS/GRAPH
device drivers. For further information, please contact Technical Support.
9 OPTIONS DEV=ACTIVEX;
10 FILENAME EGSR TEMP;
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to
"/work/sas_work/SAS_work64A600002F6A_d1sas/SAS_work025500002F6A_d1sas/"
NOTE: Writing TAGSETS.SASREPORT11(EGSR) Body file: EGSR
11 ODS tagsets.sasreport11(ID=EGSR) FILE=EGSR STYLE=EGDefault
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefau
11 ! lt.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8;
12
13 %gaccessible;
MLOGIC(GACCESSIBLE): Beginning execution.
MLOGIC(GACCESSIBLE): %LOCAL ACCESSIBLE
MLOGIC(GACCESSIBLE): %LOCAL GRAPHAVAIL
SYMBOLGEN: Macro variable SYSVLONG resolves to 9.01.01M3P020206
MLOGIC(GACCESSIBLE): %IF condition %SCAN(&SYSVLONG, 1, .) ne 8 is TRUE
MLOGIC(GACCESSIBLE): %LET (variable name is GRAPHAVAIL)
SYMBOLGEN: Macro variable GRAPHAVAIL resolves to 9.01 TS1M3
MLOGIC(GACCESSIBLE): %IF condition (%sysfunc(index(&graphavail,TS)) > 0) is TRUE
MLOGIC(GACCESSIBLE): %LET (variable name is ACCESSIBLE)
SYMBOLGEN: Macro variable ACCESSIBLE resolves to ACCESSIBLE
MLOGIC(GACCESSIBLE): %IF condition (&accessible^=) is TRUE
MPRINT(GACCESSIBLE): GOPTIONS ACCESSIBLE;
MLOGIC(GACCESSIBLE): Ending execution.
14 filename in1 "/data/dept/finance/Dups/MONTH END ALL FILES/MONTH_END_0607_GA.csv";
15 OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC MPRINT;
16
17 %MACRO _EG_ImportData(filein,yr);
18
19 DATA WORK.MONTH_END_&yr;
20 /* Enterprise Guide creates this temporary asterisk delimited text file from the
20 ! original data source */
21 INFILE &filein
22 DELIMITER='*'
23 MISSOVER
24 DSD
25 LRECL=32767
26 FIRSTOBS=2
27 ;
28 LENGTH
29 id 8
30 MEDICAID_NO $ 14
31 EFFECTIVE_DATE 8
32 MEMBER_LAST_NAME $ 18
33 MEMBER_FIRST_NAME $ 19
34 MEMBER_DOB 8
35 SEQ_MEMB_ID 8
2 The SAS System 13:34 Wednesday, December 9, 2009

36 INSERT_DATETIME 8
37 SUBSCRIBER_ID $ 15
38 PLAN_CODE $ 11
39 MEMBER_SEX $ 12
40 PS_COVERAGE_LOB $ 17
41 PS_COUNT_THE_MEMBER 8
42 PS_EXPECTED_AMOUNT 8
43 PS_REMIT_AMOUNT 8
44 PRC_PREMIUM_REPORT_CODE $ 25
45 _TYPE_ $ 8
46 _FREQ_ 8
47 cnt_N 8
48 ;
49 INFORMAT
50 EFFECTIVE_DATE ANYDTDTM22.
51 MEMBER_DOB ANYDTDTM22.
52 INSERT_DATETIME ANYDTDTM22.
53 ;
54 FORMAT
55 EFFECTIVE_DATE DATEAMPM22.0
56 MEMBER_DOB DATEAMPM22.0
57 INSERT_DATETIME DATEAMPM22.0
58 ;
59 INPUT
60 id
61 MEDICAID_NO $
62 EFFECTIVE_DATE
63 MEMBER_LAST_NAME $
64 MEMBER_FIRST_NAME $
65 MEMBER_DOB
66 SEQ_MEMB_ID
67 INSERT_DATETIME
68 SUBSCRIBER_ID $
69 PLAN_CODE $
70 MEMBER_SEX $
71 PS_COVERAGE_LOB $
72 PS_COUNT_THE_MEMBER
73 PS_EXPECTED_AMOUNT
74 PS_REMIT_AMOUNT
75 PRC_PREMIUM_REPORT_CODE $
76 _TYPE_ $
77 _FREQ_
78 cnt_N
79 ;
80 LABEL
81 id = "id"
82 MEDICAID_NO = "MEDICAID_NO"
83 EFFECTIVE_DATE = "EFFECTIVE_DATE"
84 MEMBER_LAST_NAME = "MEMBER_LAST_NAME"
85 MEMBER_FIRST_NAME = "MEMBER_FIRST_NAME"
86 MEMBER_DOB = "MEMBER_DOB"
87 SEQ_MEMB_ID = "SEQ_MEMB_ID"
88 INSERT_DATETIME = "INSERT_DATETIME"
89 SUBSCRIBER_ID = "SUBSCRIBER_ID"
90 PLAN_CODE = "PLAN_CODE"
91 MEMBER_SEX = "MEMBER_SEX"
92 PS_COVERAGE_LOB = "PS_COVERAGE_LOB"
93 PS_COUNT_THE_MEMBER = "PS_COUNT_THE_MEMBER"
3 The SAS System 13:34 Wednesday, December 9, 2009

94 PS_EXPECTED_AMOUNT = "PS_EXPECTED_AMOUNT"
95 PS_REMIT_AMOUNT = "PS_REMIT_AMOUNT"
96 PRC_PREMIUM_REPORT_CODE = "PRC_PREMIUM_REPORT_CODE"
97 _TYPE_ = "_TYPE_"
98 _FREQ_ = "_FREQ_"
99 cnt_N = "cnt_N"
100 ;
101 RUN;
102 %MEND;
103 %_EG_ImportData(in1,0607);
MLOGIC(_EG_IMPORTDATA): Beginning execution.
MLOGIC(_EG_IMPORTDATA): Parameter FILEIN has value in1
MLOGIC(_EG_IMPORTDATA): Parameter YR has value 0607
SYMBOLGEN: Macro variable YR resolves to 0607
MPRINT(_EG_IMPORTDATA): DATA WORK.MONTH_END_0607;
SYMBOLGEN: Macro variable FILEIN resolves to in1
MPRINT(_EG_IMPORTDATA): INFILE in1 DELIMITER='*' MISSOVER DSD LRECL=32767 FIRSTOBS=2 ;
MPRINT(_EG_IMPORTDATA): LENGTH id 8 MEDICAID_NO $ 14 EFFECTIVE_DATE 8 MEMBER_LAST_NAME $ 18
MEMBER_FIRST_NAME $ 19 MEMBER_DOB 8 SEQ_MEMB_ID 8 INSERT_DATETIME 8 SUBSCRIBER_ID $ 15
PLAN_CODE $ 11 MEMBER_SEX $ 12 PS_COVERAGE_LOB $ 17 PS_COUNT_THE_MEMBER 8 PS_EXPECTED_AMOUNT 8
PS_REMIT_AMOUNT 8 PRC_PREMIUM_REPORT_CODE $ 25 _TYPE_ $ 8 _FREQ_ 8 cnt_N 8 ;
MPRINT(_EG_IMPORTDATA): INFORMAT EFFECTIVE_DATE ANYDTDTM22. MEMBER_DOB ANYDTDTM22.
INSERT_DATETIME ANYDTDTM22. ;
MPRINT(_EG_IMPORTDATA): FORMAT EFFECTIVE_DATE DATEAMPM22.0 MEMBER_DOB DATEAMPM22.0
INSERT_DATETIME DATEAMPM22.0 ;
MPRINT(_EG_IMPORTDATA): INPUT id MEDICAID_NO $ EFFECTIVE_DATE MEMBER_LAST_NAME $
MEMBER_FIRST_NAME $ MEMBER_DOB SEQ_MEMB_ID INSERT_DATETIME SUBSCRIBER_ID $ PLAN_CODE $
MEMBER_SEX $ PS_COVERAGE_LOB $ PS_COUNT_THE_MEMBER PS_EXPECTED_AMOUNT PS_REMIT_AMOUNT
PRC_PREMIUM_REPORT_CODE $ _TYPE_ $ _FREQ_ cnt_N ;
MPRINT(_EG_IMPORTDATA): LABEL id = "id" MEDICAID_NO = "MEDICAID_NO" EFFECTIVE_DATE =
"EFFECTIVE_DATE" MEMBER_LAST_NAME = "MEMBER_LAST_NAME" MEMBER_FIRST_NAME = "MEMBER_FIRST_NAME"
MEMBER_DOB = "MEMBER_DOB" SEQ_MEMB_ID = "SEQ_MEMB_ID" INSERT_DATETIME = "INSERT_DATETIME"
SUBSCRIBER_ID = "SUBSCRIBER_ID" PLAN_CODE = "PLAN_CODE" MEMBER_SEX = "MEMBER_SEX"
PS_COVERAGE_LOB = "PS_COVERAGE_LOB" PS_COUNT_THE_MEMBER = "PS_COUNT_THE_MEMBER"
PS_EXPECTED_AMOUNT = "PS_EXPECTED_AMOUNT" PS_REMIT_AMOUNT = "PS_REMIT_AMOUNT"
PRC_PREMIUM_REPORT_CODE = "PRC_PREMIUM_REPORT_CODE" _TYPE_ = "_TYPE_" _FREQ_ = "_FREQ_" cnt_N =
"cnt_N" ;
MPRINT(_EG_IMPORTDATA): RUN;

ERROR: Physical file does not exist, /data/dept/finance/Dups/MONTH END ALL
FILES/MONTH_END_0607_GA.csv.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MONTH_END_0607 may be incomplete. When this step was stopped there
were 0 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MLOGIC(_EG_IMPORTDATA): Ending execution.
104
105
106 %LET _CLIENTTASKLABEL=;
107 %LET _EGTASKLABEL=;
108 %LET _CLIENTPROJECTNAME=;
109 %LET _SASPROGRAMFILE=;
110
111 ;*';*";*/;quit;run;
4 The SAS System 13:34 Wednesday, December 9, 2009

112 ODS _ALL_ CLOSE;
113
114
115 QUIT; RUN;
116

Re: EG and Macros

deleted_user — Wed, 09 Dec 2009 18:40:11 GMT

filename in1 "/data/dept/finance/Dups/MONTH END ALL FILES/MONTH_END_0607_GA.csv";

%MACRO _EG_ImportData(filein,yr);
%MEND;
% _EG_ImportData(in1,0607);

Re: EG and Macros

Flip — Wed, 09 Dec 2009 18:55:53 GMT

The import tool works on your local machine. The code runs on your server. Unless you have your server set up to read your local machines file system you are not going to access the local files.

Re: EG and Macros

deleted_user — Wed, 09 Dec 2009 19:48:49 GMT

The files are also located on the server.

Re: EG and Macros

twocanbazza — Wed, 09 Dec 2009 20:08:40 GMT

At a Guess

Is your server a windows or other flavour.... Case sensitive file names?

Re: EG and Macros

Flip — Wed, 09 Dec 2009 20:13:23 GMT

The macro seems to be resolving the name correctly. I can only suggest that you simplify things to check the filename assignment. Try just running an fexist(in1); To be sure before the macro. It just looks like an error in finding the file, not a macro problem from what I can see.

Re: EG and Macros

deleted_user — Wed, 30 Dec 2009 02:20:19 GMT

Flip,

What flavor of server do you have?

Unix and Windows handle file names/paths differently.

My understanding is Unix doesn't like blanks in a file name at all. I can't remember if windows is case-sensitive but I have been told that Unix is. Furthermore, I have been told there is a much shorter file-path length in Unix vs. Windows servers.

HTH

Tom Miller

Re: EG and Macros

Flip — Mon, 04 Jan 2010 18:57:41 GMT

Tom;
I'm not sure why you ask (I am not having any problems) but the environment I am in now is UNIX. I only use EG occasionally, give me an interactive editor session any day.

Re: EG and Macros

sbb — Mon, 04 Jan 2010 19:10:38 GMT

From what I can see, the successful execution is reading data from:

Input Data: #LN00011
Server: D1SAS

And the subsequent execution is occurring on what system/machine?

The two environments are different, unless you can explain otherwise -- suggestion is that you provide a *COMPLETE SAS LOG* output, not just choice snippets.

Scott Barry
SBBWorks, Inc.

Re: EG and Macros

Patrick — Tue, 05 Jan 2010 06:14:42 GMT

From what you describe I understand that:
The file for import is on a file-server which is mapped/mounted on your local machine as well as on the Unix server. Correct?
You run both the version with EG import wizard and the macro version with the same SAS server. Correct?
As you say "the file is also on the server": How do you know? Can you access the Unix server via a telnet session (Putty or the like) or can you access the file using SAS EG via the "Files" directory on this server?

Just one thought I had:
Could it be that you're running your SAS session under Unix with another user than the one where you checked whether the file exist under Unix? And if so: Could it be that this user for the Unix SAS server doesn't have read permission for the file to import?

HTH
Patrick

Re: EG and Macros

SUN59338 — Mon, 11 Jan 2010 19:51:36 GMT

Could you try this way?

call macro with the value of macro variable, but fileref. I wondering if this is the problem.

%let in1 = /data/dept/finance/Dups/MONTH END ALL FILES/MONTH_END_0607_GA.csv;

%MACRO _EG_ImportData(filein,yr);

DATA WORK.MONTH_END_&yr;

INFILE "&filein."
...
....
%mend;

%_EG_ImportData(&in1.,0607);