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:-
| ||||||||||||||||||
|
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.
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.
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.
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
I have reported the bug.
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.