topic Re: PROC EXPORT delimited file issue: drop treats underscore as special character in SAS Programming

PROC EXPORT delimited file issue: drop treats underscore as special character

Hkns — Mon, 04 Dec 2017 20:19:41 GMT

I am trying to export a dataset in txt (pipe delimited format) and the variable names are used with some underscores in them.

Below is the sample code:-

data work.test_issue;
input id var1 $ var1_a $ var1_b $ ;
datalines;
10 a b c
20 d e f
;

proc print data=work.test_issue;
run;
proc print data=work.test_issue(drop=var1_a);
run;

PROC EXPORT
DATA=work.test_issue(drop=var1_a )
OUTFILE='test_data.txt' DBMS=dlm REPLACE;
delimiter='|';
RUN;

PROC EXPORT
DATA=work.test_issue(drop=var1_a )
OUTFILE='test_data.xlsx' DBMS=xlsx REPLACE;
RUN;

Output of the code is:-

Obs	id	var1	var1_a	var1_b
1	10	a	b	c
2	20	d	e	f

Obs	id	var1	var1_b
1	10	a	c
2	20	d	f

Content in txt file

id|var1_b
10|c
20|f

Content in xlsx file

id	var1	var1_b
10	a	c
20	d	f

Everything is fine except for the txt file. I have dropped only the var1_a field but the txt file does not have var1 field as well along with var1_a field. Somehow the drop statement is dropping var1 field as well.

I am not able to understand if I have done anything wrong here or if there is a bug in the sas procedure.

Re: PROC EXPORT delimited file issue: drop treats underscore as special character

Reeza — Mon, 04 Dec 2017 20:32:03 GMT

Seems like a bug to me...I can replicate your issue in SAS 9.4 TS1M3

Please include your version and consider reporting it directly to SAS support.

@Hkns wrote:

I am trying to export a dataset in txt (pipe delimited format) and the variable names are used with some underscores in them.

Below is the sample code:-

data work.test_issue;
input id var1 $ var1_a $ var1_b $ ;
datalines;
10 a b c
20 d e f
;

proc print data=work.test_issue;
run;
proc print data=work.test_issue(drop=var1_a);
run;

PROC EXPORT
DATA=work.test_issue(drop=var1_a )
OUTFILE='test_data.txt' DBMS=dlm REPLACE;
delimiter='|';
RUN;

PROC EXPORT
DATA=work.test_issue(drop=var1_a )
OUTFILE='test_data.xlsx' DBMS=xlsx REPLACE;
RUN;

Output of the code is:-

Obs

id

var1

var1_a

var1_b

1

10

a

b

c

2

20

d

e

f

Obs

id

var1

var1_b

1

10

a

c

2

20

d

f

Content in txt file

id|var1_b
10|c
20|f

Content in xlsx file

id var1 var1_b

10 a c

20 d f

Everything is fine except for the txt file. I have dropped only the var1_a field but the txt file does not have var1 field as well along with var1_a field. Somehow the drop statement is dropping var1 field as well.

I am not able to understand if I have done anything wrong here or if there is a bug in the sas procedure.

Re: PROC EXPORT delimited file issue: drop treats underscore as special character

Hkns — Mon, 04 Dec 2017 23:41:44 GMT

Thanks Reeza.

If this could be of help for others, the workaround code for this issue that I have used is:

PROC SQL;
CREATE VIEW EXPORT_DATA AS
SELECT * FROM work.test_issue(drop=var1_a );
QUIT;

PROC EXPORT
DATA=EXPORT_DATA
OUTFILE='test_data.txt' DBMS=dlm REPLACE;
delimiter='|';
RUN;

This works fine but drop statement directly does not work.

Re: PROC EXPORT delimited file issue: drop treats underscore as special character

Kurt_Bremser — Tue, 05 Dec 2017 12:55:55 GMT

Can confirm the same effect with SAS 9.4 TS1M2 on AIX 64. Definitely a bug.

Used code:

data work.test_issue;
input id var1 $ var1_a $ var1_b $ ;
datalines;
10 a b c
20 d e f
;
run;

proc export
  data=work.test_issue (drop=var1_a )
  outfile='$HOME/sascommunity/test_data.txt'
  dbms=dlm
  replace
;
delimiter='|';
run;

Resulting file:

id|var1_b
10|c
20|f

Re: PROC EXPORT delimited file issue: drop treats underscore as special character

ChrisNZ — Thu, 07 Dec 2017 03:43:27 GMT

I have reported the bug.