BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
JMagenta
Obsidian | Level 7

Hello,

 

I tried all I can think of including a data Null step. However when I use a simple proc export one variable exports with double quotes and trailing blanks. It's a character variable with a length and format of $35. 

 

My Proc export looks like this: 

 

proc export data=file1
outfile="&outpath.file1..txt"
dbms=tab replace;
run;

 

Output looks like this:

Var N                                                                                              VarO            VarP         VarE

"NNNNNNN                                                                               "  OOOOO      PPPPP     EEEEEE

 

I need the output to look like this: 

Var N                  VarO            VarP         VarE

NNNNNNN       OOOOO      PPPPP     EEEEEE

 

What is happening? Everything was fine when I ran this program before. Now this. The actual dataset in SAS looks fine. The reason I didn't use a data _null_ step, besides the fact I'm not too familiar with them, is because the one I wrote was not outputting the variable headers, just the values. What am I doing wrong? Please help.

Thanks in advance,

JMagenta

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Look at the compress function with the 's' option to strip all space type characters.

View solution in original post

8 REPLIES 8
yabwon
Onyx | Level 15

You are using: dbms=tab, check if the Var N contains tabulator.

 

[EDIT:]

You can also trim trailing an leading spaces  with:

varN = strip(varN);

if the variable value will be still "in quotes" this means there is more than space.

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Tom
Super User Tom
Super User

PROC EXPORT will not add quotes around character variables for no reason.

Example:

filename txt temp;
proc export data=sashelp.class(obs=3) file=txt dbms=tab;
run;
data _null_;
  infile txt;
  input;
  list;
run;

Result:

RULE:     ----+----1----+----2----+----3----+----4-

1   CHAR  Name.Sex.Age.Height.Weight 26
    ZONE  46660567046604666670566667
    NUMR  E1D59358917598597849759784

2   CHAR  Alfred.M.14.69.112.5 20
    ZONE  46676604033033033323
    NUMR  1C62549D9149699112E5

3   CHAR  Alice.F.13.56.5.84 18
    ZONE  466660403303323033
    NUMR  1C93596913956E5984

4   CHAR  Barbara.F.13.65.3.98 20
    ZONE  46766760403303323033
    NUMR  212212196913965E3998
NOTE: 4 records were read from the infile TXT.
      The minimum record length was 18.
      The maximum record length was 26.

But SAS will add quotes around the value when it contains the delimiter.

 

So the value of the variable VARN must have one or more TAB characters in it.  If you remove the quotes then the file will not be readable because you will not know where one value ends and the next value starts.  So the only way to write that file without quotes is to either remove the tab characters or use some other delimiter that does not appear in any of the values.

 

If the quotes are causing trouble for the program that is reading the file then you need to fix that program.  Quotes around values that contain delimiters is the expected behavior for a delimited file. Without them the file is unreadable.

JMagenta
Obsidian | Level 7

Is there a way to write a data_Null step to include the variable names as headers, the variable output, that somehow makes it so that I get the desired output. I'm not seeing where the extra tabs and delimiter was added to this particular variable. It's a very simple program. 

 

Thank you,

JMagenta

Tom
Super User Tom
Super User

It is trivial to write a delimited text file without using PROC EXPORT.

To write the data without the header you just need to do something like:

data _null_;
  file 'myfile.txt' dsd dlm='09'x ;
  set have;
  put (_all_) (+0);
run;

But it won't fix the issue of the quotes being added. SAS will add the quotes when the value contains the delimiter.  If the tab is coming from the actual value of the variable (and not being generated by some format that is attached the variable) then you could just loop over all of the character variables are replace the tabs before writing the values.

data _null_;
  file 'myfile.txt' dsd dlm='09'x ;
  set have;
  array __C _character_;
  do over __C ;
    __C = translate(__C,' ','09'x);
  end;
  put (_all_) (+0);
run;

 

If you want to include a header then write that first and then append the data lines.

proc transpose data=have(obs=0) out=names;
  var _all_;
run;
data _null_;
  file 'myfile.txt' dsd dlm='09'x;
  set names;
  put _name_ @;
run;
data _null_;
  file 'myfile.txt' dsd dlm='09'x MOD ;
  set have;
  array __C _character_;
  do over __C ;
    __C = translate(__C,' ','09'x);
  end;
  put (_all_) (+0);
run;

PS You still not explain what process/program you are using to read the delimited file that complained about the quotes.

Reeza
Super User
Look at the compress function with the 's' option to strip all space type characters.
JMagenta
Obsidian | Level 7

This option did not work.

 

Thank you.

JMagenta
Obsidian | Level 7

My mistake, I was not using it correctly. This worked!

 

JMagenta

Tom
Super User Tom
Super User

@Reeza wrote:
Look at the compress function with the 's' option to strip all space type characters.

Stripping all "whitespace" characters might make the values unusable.

Probably better to just remove the tab characters.  Probably best to replace them with spaces so if they appear in the middle of the string there is still some separation between the words.

data for_export;
  set have;
  varn = translate(varn,' ','09'x);
run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1117 views
  • 4 likes
  • 4 in conversation