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

How to avoid space in .txt file during PROC EXPORT

mlogan — Tue, 24 May 2016 15:32:47 GMT

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

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

ballardw — Tue, 24 May 2016 15:47:20 GMT

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.

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

mlogan — Tue, 24 May 2016 15:57:18 GMT

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.

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

Tom — Tue, 24 May 2016 16:08:12 GMT

Use a formatted PUT statement instead.

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

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

ballardw — Tue, 24 May 2016 16:14:35 GMT

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.

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

mlogan — Tue, 24 May 2016 19:55:24 GMT

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,

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

Tom — Tue, 24 May 2016 20:04:15 GMT

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

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

mlogan — Tue, 24 May 2016 20:15:14 GMT

Thanks Tom. it worked.

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

mlogan — Wed, 25 May 2016 00:57:02 GMT

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.

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

mlogan — Wed, 25 May 2016 00:58:30 GMT

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.

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

Tom — Wed, 25 May 2016 01:17:26 GMT

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 ;

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

mlogan — Thu, 26 May 2016 14:22:21 GMT

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

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

mlogan — Tue, 31 May 2016 17:11:14 GMT

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.