I am trying to convert sas dataset(containg nearly 9 Mil records) to text file using below code
In sas dataset also line is breaking
Data workdir.CCDUPSF(keep = PCEKEY SORTKEY FILL);
Set workdir.CCORX01WTEMP;
LENGTH SORTKEY $42 FILL $2289;
SORTKEY= COMPBL(PCEKEY || FILL2);
FILL = COMPBL(FILL1 || FILL2 || FILL3);
run;
DATA _NULL_;
SET workdir.CCDUPSF;
FILE '$CURRPATH/YGRT010.txt';
PUT @0001 PCEKEY $40.
PUT @0041 FILL $2289.;
RUN;
But while writing it to text file , getting line break on some of the obs.
can anyone suggest to fix this line break.
This is hapenning for some obs not all the obs
@animesh123 wrote:
So this is for infile ,am not reading the file
I am trying to write the data using this code
DATA _NULL_;
SET workdir.CCDUPSF;
FILE '$CURRPATH/YGRT010.txt';
PUT @0001 PCEKEY $40.
PUT @0041 FILL $2289.;
RUN;
We do not seem to be communicating. The example I showed had one data step to create some example data so that everyone can work from the same source. Then a step to write that to a file, like your step. Then other steps to check the resulting contents of the file that was just written. Please read the example code I posted and apply it to your situation. You need to REMOVE the LINE FEED ('0A'x) and CARRAIGE RETURN ('0d'x) characters from the data before running the PUT statement.
Also the error in your data step is still there. You have one PUT statement that spans two lines of code. It is writing three variables, one of which is named PUT. But I do not think that is what is causing your extra lines (unless you actually have a variable named PUT that contains linefeed or carriage return characters).
You could use one PUT statement to write both fields on the same line.
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40. @0041 fill $2289.;
run;
If you want to use two PUT statements
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40.;
put @0041 fill $2289.;
run;
You will write TWO lines for every observation in your dataset. If you want to use two PUT statements and write one line instead then add a trailing @ to the first one to prevent the line from being ended too early.
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40. @ ;
put @0041 fill $2289.;
run;
getting output data like this for FILL
business, on bureau since '17. (Line brk)
limited clean credit, a1 fcc hist. (Line brk)
replacing unit. DBA: ROSE HILL (Line brk)
It means you have carriage return (hex 0D) and/or linefeed (hex 0A) characters in your strings. Also add a LRECL=2400 option to your FILE statement.
yes , I also try to add LRECL=2600 but still the line is breaking
Can we add something here to fix this:
Data workdir.CCDUPSF(keep = PCEKEY SORTKEY FILL);
Set workdir.CCORX01WTEMP;
LENGTH SORTKEY $42 FILL $2289;
SORTKEY= COMPBL(PCEKEY || FILL2);
FILL = COMPBL(FILL1 || FILL2 || FILL3);
run;
As long as you have CR/LF characters in your strings, the behavior will not change.
You are missing a semicolon to end your first PUT statement. Either add the missing semicolon or remove the extra PUT keyword so that you write both variables with the same PUT statement.
But that is probably not your real problem. Most likely the text you are writing with the PUT statements contain the ASCII codes that mark the end of a line. LF on Unix or CRLF on Windows.
Look at the text file with something that allows you to see the characters it contains. Notepad+ I think allows this. Or just use a SAS data step with the LIST statement. Read the file as fixed length records instead of "lines".
data _null_;
infile '$CURRPATH/YGRT010.txt' lrec=100 recfm=f ;
input;
list;
run;
Or display the values of you character variable using the $HEX format to see the ASCII codes for the values.
Once you see what characters are there, look for '0A'x which is a Line Feed or '0D'x which is a Carriage Return, then modify our code to REMOVE or REPLACE those characters.
For example by translating them into space characters instead.
DATA _NULL_;
SET workdir.CCDUPSF;
fill = translate(fill,' ','0A0D'x);
FILE '$CURRPATH/YGRT010.txt';
PUT @0001 PCEKEY $40. ;
PUT @0041 FILL $2289.;
RUN;
Your use of @ pointer controls and fixed length formats makes it look like perhaps you want to create a file with FIXED LENGTH records (essentially a binary file even if all of the bytes are valid ASCII characters) instead of a text file with variable length LINES of text.
If that is the case then whether or not the text being written has embedded end of line characters should not matter (as long as you read it as fixed length records (binary file) as well). Since 40 + 2289 is 2329 looks like you want a record length of 2329.
DATA _NULL_;
SET workdir.CCDUPSF;
FILE '$CURRPATH/YGRT010.txt' recfm=f lrecl=2329 ;
PUT PCEKEY $40. FILL $2289.;
RUN;
@animesh123 wrote:
Example:-
with standard rate. Added room for
backend. Trading open auto. Well
pd autos and mtg. Additional
household income evident. Please ,
Even tried to do the same with proc export ,but result is same
Let' first recreate your example as a text string with embedded end of line characters.
data have;
example ='with standard rate. Added room for' || '0D0A'x
|| 'backend. Trading open auto. Well' || '0D0A'x
|| 'pd autos and mtg. Additional' || '0D0A'x
|| 'household income evident. Please ,'
;
run;
Now we can try writing it to a text file.
36 filename text temp; 37 data _null_; 38 set have; 39 file text ; 40 put example; 41 run; NOTE: The file TEXT is: Filename=C:\Users\...\#LN00010, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=05Nov2024:07:34:19, Create Time=05Nov2024:07:34:19 NOTE: 1 record was written to the file TEXT. The minimum record length was 134. The maximum record length was 134. NOTE: There were 1 observations read from the data set WORK.HAVE. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
So one line was written. But because the content included the end of line characters when you read it back in as a simple text file there are instead four lines read.
43 data _null_; 44 infile text; 45 input; 46 list ; 47 run; NOTE: The infile TEXT is: Filename=C:\Users\...\#LN00010, RECFM=V,LRECL=32767,File Size (bytes)=136, Last Modified=05Nov2024:07:34:19, Create Time=05Nov2024:07:34:19 RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 1 with standard rate. Added room for 34 2 backend. Trading open auto. Well 32 3 pd autos and mtg. Additional 28 4 household income evident. Please , 34 NOTE: 4 records were read from the infile TEXT. The minimum record length was 28. The maximum record length was 34. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
But if we read it as binary (fixed length) records we can see the actual end of line characters (and their actual hexcodes). For example notice that there is OD and OA characters in the 35 and 36th byte of the file.
49 data _null_; 50 infile text recfm=f lrecl=100; 51 input; 52 list; 53 run; NOTE: The infile TEXT is: Filename=C:\Users\...\#LN00010, RECFM=F,LRECL=100,File Size (bytes)=136, Last Modified=05Nov2024:07:34:19, Create Time=05Nov2024:07:34:19 RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 1 CHAR with standard rate. Added room for..backend. Trading open auto. Well..pd autos and mtg. Additional.. ZONE 7676277666676276762246666276662667006666666225766666267662677622566600762677672666267622466676666600 NUMR 79480341E412402145E01445402FFD06F2DA213B5E4E042149E70F05E0154FE075CCDA040154F301E40D47E0144949FE1CDA 2 CHAR household income evident. Please ,.. 36 ZONE 667766666266666626766667225666762200 NUMR 8F5358FC409E3FD5056945E4E00C51350CDA NOTE: 2 records were read from the infile TEXT. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
But if we replace those characters with spaces.
54 filename text temp; 55 data _null_; 56 set have; 57 file text ; 58 example=translate(example,' ','0D0A'x); 59 put example; 60 run; NOTE: The file TEXT is: Filename=C:\Users\...\#LN00011, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=05Nov2024:07:40:27, Create Time=05Nov2024:07:40:27 NOTE: 1 record was written to the file TEXT. The minimum record length was 134. The maximum record length was 134. NOTE: There were 1 observations read from the data set WORK.HAVE. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 61 62 data _null_; 63 infile text; 64 input; 65 list ; 66 run; NOTE: The infile TEXT is: Filename=C:\Users\...\#LN00011, RECFM=V,LRECL=32767,File Size (bytes)=136, Last Modified=05Nov2024:07:40:27, Create Time=05Nov2024:07:40:27 RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 1 with standard rate. Added room for backend. Trading open auto. Well pd autos and mtg. Additional 101 household income evident. Please , 134 NOTE: 1 record was read from the infile TEXT. The minimum record length was 134. The maximum record length was 134. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds
Now there is only one line in the text file.
So this is for infile ,am not reading the file
I am trying to write the data using this code
DATA _NULL_;
SET workdir.CCDUPSF;
FILE '$CURRPATH/YGRT010.txt';
PUT @0001 PCEKEY $40.
PUT @0041 FILL $2289.;
RUN;
@animesh123 wrote:
So this is for infile ,am not reading the file
I am trying to write the data using this code
DATA _NULL_;
SET workdir.CCDUPSF;
FILE '$CURRPATH/YGRT010.txt';
PUT @0001 PCEKEY $40.
PUT @0041 FILL $2289.;
RUN;
We do not seem to be communicating. The example I showed had one data step to create some example data so that everyone can work from the same source. Then a step to write that to a file, like your step. Then other steps to check the resulting contents of the file that was just written. Please read the example code I posted and apply it to your situation. You need to REMOVE the LINE FEED ('0A'x) and CARRAIGE RETURN ('0d'x) characters from the data before running the PUT statement.
Also the error in your data step is still there. You have one PUT statement that spans two lines of code. It is writing three variables, one of which is named PUT. But I do not think that is what is causing your extra lines (unless you actually have a variable named PUT that contains linefeed or carriage return characters).
You could use one PUT statement to write both fields on the same line.
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40. @0041 fill $2289.;
run;
If you want to use two PUT statements
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40.;
put @0041 fill $2289.;
run;
You will write TWO lines for every observation in your dataset. If you want to use two PUT statements and write one line instead then add a trailing @ to the first one to prevent the line from being ended too early.
data _null_;
set workdir.ccdupsf;
file "$currpath/ygrt010.txt";
put @0001 pcekey $40. @ ;
put @0041 fill $2289.;
run;
For this I did some more investigation to find out variable which causing issue
So I found like 2 or 3 variable which have this issue pasting one as an e.g
DECIS_CMT_TX
vm for Karina, will need cash added
to offering. see condition. future
offerings may require a line of
credit.
I am getting this data from database itself like above pasted ,
As I am trying to write the same into text file the format is coming same as we fetched from database
Is there anyway to fix this on variable itself?
You have been told repeatedly what to do:
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.