How to avoid space in .txt file during PROC EXPORT

[mlogan]

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

[ballardw]

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.

[mlogan]

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.

[ballardw]

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.

[mlogan]

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.

[mlogan]

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.

[Tom · Accepted solution]

Use a formatted PUT statement instead.

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

[mlogan]

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,

[Tom]

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 ...

[mlogan]

Thanks Tom. it worked.

[mlogan]

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.

[Tom]

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 ;

[mlogan]

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