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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.