DATA Step, Macro, Functions and more

How to avoid space in .txt file during PROC EXPORT

Accepted Solution Solved
Reply
Regular Contributor
Posts: 218
Accepted Solution

How to avoid space in .txt file during PROC EXPORT

Hi All,

I have the following dataset. Each Varable length is exact length. For example patient_id has length 3 and variable_1 has legth 7.

When I export the dataset to .txt I get space between two variable (since I indicate dbms=tab in my output). Can anyone tell me how do I specify in PROC EXPORT so that I can avoid spcae in .txt file. Thanks,

 

patient_ID Var_1         Var_2         Var_3
101           LP28M72   LP23M66   LP22M64
101           LP29M64   NOTUSED NOTUSED
102           SR66F76   SR62F76   SR61F76
102           SR69F76   SR77F76   NOTUSED
103           JH23F56   JH43F56   NOTUSED

 

here is my code:

 

PROC EXPORT
data=have
outfile='C:\Users\want.txt'
dbms=tab replace;
putnames=no;
run;

EXPECTED OUTPUT:

 

101LP28M72LP23M66LP22M64
101LP29M64NOTUSEDNOTUSED
102SR66F76SR62F76SR61F76
102SR69F76SR77F76NOTUSED
103JH23F56JH43F56NOTUSED

 


Accepted Solutions
Solution
‎05-24-2016 12:37 PM
Super User
Super User
Posts: 7,076

Re: How to avoid space in .txt file during PROC EXPORT

Use a formatted PUT statement instead.

 

data _null_;
  file 'want.txt' ;
  set have ;
  put patient_ID $3. (Var_1-Var_3) ($7.) ;
run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How to avoid space in .txt file during PROC EXPORT

If you don't want space between variables then why specify TAB in the first place?

If I understand your requirement you may need to go old school as Proc Export want some sort of file type and a delimiter, so you would get a character between values.

 

Something like this may give you what you want:

data _null_;
   set have;
   file 'C:\Users\want.txt' ;
   length line $ 25; /* large enough to hold all the variables*/
   line = catt(patientid,var_1,var_2,var_3);
   put line;
run;

Though Fixed column isn't popular anymore.

 

Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi ballardw,
Thanks for your reply. If I use catt then my variable will be 750 character long as my each variable is actually 35 character long, that's why I don't think putting all variables in one will work for me. That's why I wanted to go for PROC EXPORT. Anyways, do you know a way which will keep the variable side by side without any space? Thanks.
Super User
Posts: 11,343

Re: How to avoid space in .txt file during PROC EXPORT

There's nothing wrong with making line 750 characters long. The only trick is making the CATT statement.

If you aren't making a whole bunch of these files then use proc export with a space or other character and then use a text editor to remove all of them. Not slick and the extra step isn't really a good idea.

 

The real old school approach was to use Put statements with fixed column start points, which may be a good idea if you ever run into a variable with varying length:

 

Put @1 Id @36 Var_1 @71 var_2 @106 var_3 <etc>   ;

Would place them at fixed positions. There is syntax that lets specify that with parantheses and variable lists but research the PUT statement for more details on Column output.

Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi ballardw,
I added few variables using PUT function, but it doesn't work after it reaches $ 256. Do you have any idea why? Thanks.
Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi ballardw,
I like your this code as an alternate of proc export. Is there any such code available for PROC IMPORT which can be an alternative of the following:

PROC IMPORT DATAFILE="C:\have.xlsx"
DBMS=xlsx replace
OUT=want (RENAME=(var3=var4) KEEP=var4 WHERE=(var1='abcd');
SHEET='steet1';
RUN;

Thanks.
Solution
‎05-24-2016 12:37 PM
Super User
Super User
Posts: 7,076

Re: How to avoid space in .txt file during PROC EXPORT

Use a formatted PUT statement instead.

 

data _null_;
  file 'want.txt' ;
  set have ;
  put patient_ID $3. (Var_1-Var_3) ($7.) ;
run;
Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi Tom,
Would you please tell me If I want to allow 12 spaces between patient_ID and Var_1 how can I do that?

Thanks,
Super User
Super User
Posts: 7,076

Re: How to avoid space in .txt file during PROC EXPORT

[ Edited ]

Look at the manual on the PUT statement.

If you want to skip 12 spaces then just at +12 to the PUT statement.

put id $3. +12 var1 $7. ....

If you want to tell SAS to use 15 instead of 3 columns for the ID variable then just use a longer format.

put id $15. ....

Or you can also tell it to go the particular column where you want to put the variable.

put id $3. @16 var1 $7. ...

You can combine them in many ways.  Just don't try to put more characters than can fit or else the values will overwrite each other.

put @15 var1 @1 id @22 var2 ...
Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Thanks Tom. it worked.
Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi Tom,
I added few variables using PUT function, but it doesn't work after it reaches $ 256. Do you have any idea why? Thanks.
Super User
Super User
Posts: 7,076

Re: How to avoid space in .txt file during PROC EXPORT

Most likely that is the default for the LRECL option on your verison of SAS.

 

Add the LRECL= option to the FILE statement.  You can set it really large as SAS will only write the number of characters you actually tell it to in the PUT statement. 32767 is a nice number to use since it is 2**15-1. 

 

file 'myfile.txt' lrecl=32767 ;

 

 

Regular Contributor
Posts: 218

Re: How to avoid space in .txt file during PROC EXPORT

Hi Tom,
Is it possible to define a variable length during concatenation? Like: var1=var2 5.||var3 10.; ? Thanks.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 501 views
  • 2 likes
  • 3 in conversation