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

[Hkns]

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. 

[Reeza]

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. 

---

[Hkns]

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.

[Kurt_Bremser]

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

[ChrisNZ]

I have reported the bug.