12-26-2017 02:26 AM - edited 12-26-2017 07:47 AM
I'm trying to fetch two files each for different location ( different folders for each location) based on a given condition and have written the following code for it. The macros section is running successfully without any errors but i'm not getting any result.
/*To fetch the list of locations*/
Proc Import Out= Location Datafile= "C:\Foxpro\LOCATION.DBF" dbms=dbfmemo Replace; Run; Data Location1; Set Location; Where ACTIVE = 'Y'; Run;
Proc Sql; Select Count(*) INTO :CNT From Location1; Quit; %Put &CNT; /*Identifying Path for each location folder*/ Proc Sql; Select LOCA_CODE, LOCA_NAME, ZONE, 'C:\Foxpro\xxxx\'||DIR_PATH as DIR_PATH INTO: LOCA_CODE1 - :LOCA_CODE%LEFT(%TRIM(&CNT)), : LOCA_NAME1 - :LOCA_NAME%LEFT(%TRIM(&CNT)), : ZONE1 - :ZONE%LEFT(%TRIM(&CNT)), : DIR_PATH1 - :DIR_PATH%LEFT(%TRIM(&CNT)) From Location1; Quit;
/* no error no result section */ %MACRO LOOP_LOAD; %DO I = 1 %TO &CNT; FILENAME T_&I. "&&DIR_PATH1&I..INVMAST.DBF"; FILENAME T_&I. "&&DIR_PATH1&I..RETUMAST.DBF"; %LET LOCA_CODE1 = &&LOCA_CODE1&I.; %LET LOCA_NAME1 = &&LOCA_NAME1&I.; %LET ZONE1 = &&ZONE1&I.; %LET DIR_PATH1 = &&DIR_PATH1&I.; %PUT &LOCA_CODE1; %PUT &LOCA_NAME1; %PUT &ZONE1; %PUT &DIR_PATH1; PROC IMPORT OUT = INVMAST_&I. DATAFILE = "T_&I." DBMS = DBFMEMO REPLACE; Run; PROC IMPORT OUT = RETUMAST_&I. DATAFILE = "T_&I." DBMS = DBFMEMO REPLACE; Run; DATA INVMAST_&I.; SET INVMAST_&I.; _LOCA_CODE1 = "&LOCA_CODE1"; _LOCA_NAME1 = "&LOCA_NAME1"; _ZONE1 = "&ZONE1"; RUN; DATA RETUMAST_&I.; SET INVMAST_&I.; _LOCA_CODE1 = "&LOCA_CODE1"; _LOCA_NAME1 = "&LOCA_NAME1"; _ZONE1 = "&ZONE1"; RUN; %END; %MEND LOOP_LOAD;
Please help in identifying as to where I'm going wrong and what I should do.
12-26-2017 09:32 AM
Parts of your code refer to macro variables that don't exist. Consider a few issues with the code below.
First, the name in a FILENAME statement (T_&I.) can only refer to one file at a time. It can't refer to both the INVMAST file and the RETUMAST file.
Second, there is no such macro variable as &DIR_PATH11. But that is what this code resolves into when &I is 1:
Perhaps you meant to use &&DIR_PATH_&I.. instead (although what you meant to do is up to you).
Finally, note that you likely need three dots not two in that expression. As it stands now, the resolution process removes all dots before INVMAST and RETUMAST.
Now that's for starters. Once you eliminate the known errors, it is possible that others will surface.
FILENAME T_&I. "&&DIR_PATH1&I..INVMAST.DBF"; FILENAME T_&I. "&&DIR_PATH1&I..RETUMAST.DBF";