BookmarkSubscribeRSS Feed
Hkns
Calcite | Level 5

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. 

4 REPLIES 4
Reeza
Super User

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 562 views
  • 0 likes
  • 4 in conversation