topic Re: Proc Export: Removing spaces in generated file in SAS Programming

Proc Export: Removing spaces in generated file

sg16773 — Mon, 24 Jun 2019 08:50:38 GMT

Hi,

I have multiple sas datsets(~30) present in a directory and need to convert all these into text files. I am using the code below for same:

=====================================================

LIBNAME Test "D:\";

%MACRO Convert2CSV(Libname);

DATA MEMBERS;
SET SASHELP.VMEMBER(WHERE=(LIBNAME = "&Libname"));

RETAIN OBS 0;
OBS = OBS+1;

KEEP MEMNAME OBS;

RUN;

PROC SQL;
SELECT MIN(OBS) INTO :MIN
FROM MEMBERS;
QUIT;

PROC SQL;
SELECT MAX(OBS) INTO :MAX
FROM MEMBERS;
QUIT;

%Local D;

%DO D = &MIN %TO &MAX;

PROC SQL;

SELECT COMPRESS(MEMNAME) INTO: Table
FROM MEMBERS
WHERE OBS=&D;

QUIT;

PROC EXPORT DATA=&Libname..&Table DBMS=dlm replace
OUTFILE="D:\_today\&Table..txt";
delimiter="|";
RUN;

%END;

%MEND;

%Convert2CSV(TEST);

==================================================================

Note: I have leveraged this code from (https://communities.sas.com/t5/SAS-Programming/Export-multiple-tables-from-SAS-to-csv/td-p/27090)

Issue: The generated text files have spaces in the name. For eg: 'myfile.sas7bat' gets converted to 'myfile .txt'

Just a hunch but I think the generated filenames are of fixed length. Therefore to meet the length, SAS is introducing spaces. How do avoid this?

Ask: I need help in removing the spaces from the generated file name. I have tried using the Trim function but it doesn't seems to be working.

Thanks,

Re: Proc Export: Removing spaces in generated file

Kurt_Bremser — Mon, 24 Jun 2019 08:56:38 GMT

Why don't you just do it in one data step that creates the exports with call execute?

data _null_;
set sashelp.vmember (where=(libname = "TEST"));
call execute('
  proc export
    data=' || strip(memname) || '
    dbms=dlm
    replace
    outfile="D:\_today\' || strip(lowcase(memname)) || '.txt"
  ;
  delimiter="|";
  run;
';
run;

Re: Proc Export: Removing spaces in generated file

sg16773 — Mon, 24 Jun 2019 09:10:30 GMT

Hi KurtBremser,

Thanks for your response. I tried the code you suggested but I am getting the following error(screenshot below). May be the code is not able to search the dataset in the defined library("TEST").

Apologies for the silly questions but I am very new to SAS and trying to learn the language.

Re: Proc Export: Removing spaces in generated file

Kurt_Bremser — Mon, 24 Jun 2019 09:29:43 GMT

I forgot to add the libname in my call execute. Expand the call execute like this.

call execute("
  proc export
    data=test." || strip(memname) || "
    dbms=dlm
    replace
    outfile='D:\_today\" || strip(lowcase(memname)) || ".txt'
  ;
  delimiter='|';
  run;
";

Note that I also changed the usage of single and double quotes, to facilitate the use of a macro variable/parameter if you later want to wrap the whole data step into a macro definition for easier reuse.

Re: Proc Export: Removing spaces in generated file

sg16773 — Mon, 24 Jun 2019 10:18:31 GMT

Thanks KurtBremser!

This did the trick 🙂