BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

12 REPLIES 12
ballardw
Super User

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
Lapis Lazuli | Level 10
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
Super User

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
Lapis Lazuli | Level 10
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
Lapis Lazuli | Level 10
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
Super User Tom
Super User

Use a formatted PUT statement instead.

 

data _null_;
  file 'want.txt' ;
  set have ;
  put patient_ID $3. (Var_1-Var_3) ($7.) ;
run;
mlogan
Lapis Lazuli | Level 10
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
Super User Tom
Super User

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
Lapis Lazuli | Level 10
Thanks Tom. it worked.
mlogan
Lapis Lazuli | Level 10
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
Super User Tom
Super User

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
Lapis Lazuli | Level 10
Hi Tom,
Is it possible to define a variable length during concatenation? Like: var1=var2 5.||var3 10.; ? Thanks.

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
  • 12 replies
  • 3556 views
  • 2 likes
  • 3 in conversation