I am using SAS Enterprise Guide 7.1 and SAS 9.4. We are currently converting from Windows to SAS Grid (Linux) and a program I inherited is not working. I changed the directory to match the Linux syntax, but it give me an error "No such file or directory exists. I know the location is fine, I run other programs there. Basically the program aggregates the rows in a tab called "delete" in a few excel files, and puts them into one file which I download to excel.
I think the problem has to do with a macro statement %str(/%" /A-D/B/ON%') . Anyway, if anyone know what the issue might be, I would be grateful for some input. The first program listed is the windows version that works. The second one is the Linux version.
THIS WORKS IN THE WINDOWS ENVIRONMENT
%macro MultImp(dir=,out=);
%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);
data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;
fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
proc import dbms=xlsx out= _test
datafile= '||fname||' replace;
Sheet = "delete";
run;
proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
');
run;
filename myfiles clear;
%mend;
%MultImp(dir=\\fnbmcorp\share\Risk\Bellagio\CMAM\Credit Bureau Cleanup\Tessa101619,out=combined);
data final(keep=CreditOneAcctNumber SSN LastName FirstName MiddleName AddressLine14 AddressLine25 City6 StateCode7
ZipCode8 OpenDate EquifaxNumber);
format EquifaxNumber $10.;
set combined;
EquifaxNumber ='180BB27505';
RUN;
proc export data=final
OUTFILE= "\\fnbmcorp\share\Risk\Bellagio\CMAM\Credit Bureau Cleanup\Combined\Cleanup_12_191.xlsx"
DBMS=XLSX REPLACE;
SHEET = Equifax;
RUN;
THIS ONE BELOW DOES NOT WORK.
%macro MultImp(dir=,out=);
%let rc=%str(%'dir %")&dir.%str(/%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);
data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;
fname=quote(cats("&dir",'/',myfiles));
out="&out";
drop myfiles;
call execute('
proc import dbms=xlsx out= _test
datafile= '||fname||' replace;
Sheet = "delete";
run;
proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
');
run;
filename myfiles clear;
%mend;
%MultImp(dir=/sasdata/area51/group/CMAM/CreditBureauCleanup/,out=combined);
data final(keep=CreditOneAcctNumber SSN LastName FirstName MiddleName AddressLine14 AddressLine25 City6 StateCode7
ZipCode8 OpenDate EquifaxNumber);
format EquifaxNumber $10.;
set combined;
EquifaxNumber ='180BB27505';
RUN;
proc export data=final
OUTFILE= "/sasdata/area51/group/CMAM/CreditBureauCleanup/Cleanup_12_test.xlsx"
DBMS=XLSX REPLACE;
SHEET = Equifax;
RUN;
NOTE: The infile MYFILES is:
Pipe command="dir "/sasdata/area51/group/CMAM/CreditBureauCleanup//" /A-D/B/ON"
NOTE: 7 records were read from the infile MYFILES.
2 The SAS System 11:23 Thursday, December 19, 2019
The minimum record length was 49.
The maximum record length was 72.
NOTE: The data set WORK.LIST has 7 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: CALL EXECUTE generated line.
1 + proc import dbms=xlsx out= _test datafile= "/sasdata/area51/group/CMAM/CreditBureauCleanup//dir: cannot
access /A-D/B/ON: No such file or directory"
2 + replace;
2 + Sheet = "delete"; run;
ERROR: Physical file does not exist, /sasdata/area51/group/CMAM/CreditBureauCleanup//dir: cannot access /A-D/B//ON: No such file or
directory.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
data=_test base=combined force; run; proc delete data=_test; run;
ERROR: File WORK._TEST.DATA does not exist.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
dir is probably not a valid command on linux whereas ls (lowercase L and S) is the equivalent.
edit: Looks like it might be working but don't know if - /A-D/B/ON% - are valid switches on linux. I suggest you test in a linux remote login session.
dir is probably not a valid command on linux whereas ls (lowercase L and S) is the equivalent.
edit: Looks like it might be working but don't know if - /A-D/B/ON% - are valid switches on linux. I suggest you test in a linux remote login session.
The program runs now, but it ends up with no columns in the output. Should I just take out the switches?
ERROR: Physical file does not exist, /sasdata/area51/group/CMAM/CreditBureauCleanup//ls: cannot access /A-D/B//ON: No such file or
directory.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
2 + proc append
data=_test base=combined force; run; proc delete data=_test; run;
ERROR: File WORK._TEST.DATA does not exist.
@pangea17 - Yes, try without the switches to see if it removes the errors.
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 25. 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.