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

Reply
New Contributor
Posts: 2

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

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

idvar1var1_b
10ac
20df

 

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. 

Super User
Posts: 21,572

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

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. 


 

 

New Contributor
Posts: 2

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

[ Edited ]

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.

Super User
Posts: 8,590

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

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,935

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

Posted in reply to KurtBremser

I have reported the bug.

Ask a Question
Discussion stats
  • 4 replies
  • 187 views
  • 0 likes
  • 4 in conversation