topic Re: Reading data from SAS7BDAT FILE and converting it into txt file issue in SAS Programming

Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Tue, 12 Nov 2024 19:32:33 GMT

So I am trying to write the below code into txt file using the below format from SAS7BDAT file,

But the output I am getting is not in the correct format as expected (Like this I have 120 variable which i am trying to convert in to txt file)

DATA _NULL_;
SET workdir.RPLPRNLN010;
FILE '$CURRPATH/NGRT.txt';
put @0001 CUST_NB $16 ;
put @0017 PCE_OFFR_DT 10 ;
put @0027 PCE_OFFR_SQNC_NB 2 ;

put @0029 PCE_ORIG_BRANCH_CD $2 ;
put @0031 PCE_CO_BUS_CD $2 ;
put @0033 PCE_ACCT_NB $11 ;
put @0037 LAST_NM $40 ;
put @0077 FIRST_NM $15 ;
put @0092 MDDL_INTL_NM $4;
put @0093 GNRT_NM $5 ;
put @0098 BIRTH_DT $10 ;
put @0108 ADDR_LINE1_TX $40 ;
put @0148 ADDR_LINE2_TX $40 ;
put @0188 CITY_NM $30
put @0218 ST_ABBR_CD $5 ;
put @0223 ZIP_CD $11 ;
put @0235 CMMRCL_IN $1 ;
put @0236 ECR_REJ_IN $1 ;
put @0237 REPO_IN $1 ;
put @0238 APPLCNT_CD $2 ;
put @0240 ACCT_CD $2 ;
put @0242 DECIS_CD $1 ;

run;

and the ouput is

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Kurt_Bremser — Tue, 12 Nov 2024 19:42:31 GMT

Use only one PUT statement with all variables in it. Each single PUT will cause a linebreak (unless you use the @ modifier).

Maxim 1: Read the Documentation!

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Patrick — Tue, 12 Nov 2024 20:40:16 GMT

Each put statement will write to a new line unless you instruct it via the @ to not do so.

Below two syntax options that will work.

/* option 1 */
put @0001 CUST_NB $16. @ ;
put @0017 PCE_OFFR_DT 10. @ ;
...

/* options 2 */
put @0001 CUST_NB $16.
    @0017 PCE_OFFR_DT 10.
    ...
    ;

Also ensure that you end formats with a full stop.

And of course use the correct format. Based on name PCE OFFR_DT looks like a variable containing a SAS Date value and though you want likely to use a SAS Date format in order to write a human readable date value to a text file.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

ballardw — Tue, 12 Nov 2024 21:21:07 GMT

The "random" single characters you are seeing are because your "formats" for writing do not have a dot ending them.

As mentioned the PUT writes one line for each PUT encountered unless the @ to hold a line for output is used.

The * you see may also be caused by incorrect format for a variable. Does your log show any statement(s) similar to:

WARNING: Variable XXXXXX has already been defined as numeric.

That would occur for any numeric variable that you have attempted to use a character, $, format with.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Tom — Wed, 13 Nov 2024 00:17:57 GMT

So you have an existing SAS dataset and you want to generate a fixed position text file.

Your PUT statements are wrong. You don't really need more than one PUT statement. If you do use more than one PUT statement then you need to code all but the last one with a trailing @ sign so that the line is not written too early. The format specification needs to include a period so the SAS parser can distinguish them from other things like variable names or numbers. You don't need all the leading zeros in the column positions you have with the @ cursor movement commands, but they don't hurt anything. It does not make sense to write 4 characters for the middle initial and then immediately overwrite all but the first character with the grant number.

data _null_;
  set workdir.rplprnln010;
  file '$currpath/NGRT.txt';
  put
   @0001 cust_nb $16. 
   @0017 pce_offr_dt 10. 
   @0027 pce_offr_sqnc_nb 2. 
   @0029 pce_orig_branch_cd $2. 
   @0031 pce_co_bus_cd $2. 
   @0033 pce_acct_nb $4.  /* From 33 thru 36 is only 4 characters */ 
   @0037 last_nm $40.
   @0077 first_nm $15. 
   @0092 mddl_intl_nm $1. /* Only room for 1 character */
   @0093 gnrt_nm $5.
   @0098 birth_dt $10.
   @0108 addr_line1_tx $40. 
   @0148 addr_line2_tx $40. 
   @0188 city_nm $30.
   @0218 st_abbr_cd $5. 
   @0223 zip_cd $11.   /* Why are you skipping a column here? */
   @0235 cmmrcl_in $1.
   @0236 ecr_rej_in $1. 
   @0237 repo_in $1. 
   @0238 applcnt_cd $2. 
   @0240 acct_cd $2. 
   @0242 decis_cd $1. 
  ;
run;

And if you don't actually need to skip any columns then you don't need the @ cursor movement commands at all since you have supplied a format for each value being written. You could always use the + cursor movement command to skip some columns if you want.

data _null_;
  set workdir.rplprnln010;
  file '$currpath/NGRT.txt';
  put
    cust_nb $16. 
    pce_offr_dt 10. 
    pce_offr_sqnc_nb 2. 
    pce_orig_branch_cd $2. 
    pce_co_bus_cd $2. 
    pce_acct_nb $4.  /* From 33 thru 36 is only 4 characters */ 
    last_nm $40.
    first_nm $15. 
    mddl_intl_nm $1. /* Only room for 1 character */
    gnrt_nm $5.
    birth_dt $10.
    addr_line1_tx $40. 
    addr_line2_tx $40. 
    city_nm $30.
    st_abbr_cd $5. 
    zip_cd $11. +1  /* Why are you skipping a column here? */
    cmmrcl_in $1.
    ecr_rej_in $1. 
    repo_in $1. 
    applcnt_cd $2. 
    acct_cd $2. 
    decis_cd $1. 
  ;
run;

And since ZIP_CD is character you could just print it with $12. format and eliminate the +1 cursor command.

Note it is not clear what your photograph is an image of. What are the thin vertical lines? Note that a text file is made up of text so no need to go to the trouble of taking a picture of it to share. You can just copy and paste a few of the lines from the file into the pop-up window that the Insert Code button creates. That is the way to share text that do not want to be reflowed by this forum into paragraphs.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Wed, 13 Nov 2024 12:48:23 GMT

Hi Thanks for your quick response

I have tried with the updated code that works fine

Reading data from SAS7BDAT FILE and converting it into txt file

Issue -But the obs count has increased from read 10 obs and writing it to 22 obs

Here the sample output:

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

LinusH — Wed, 13 Nov 2024 08:49:11 GMT

Show your code and log for this execution.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

ballardw — Wed, 13 Nov 2024 10:18:55 GMT

@animesh123 wrote:

Hi Thanks for your quick response

I have tried with the updated code that works fine

Reading data from SAS7BDAT FILE and converting it into txt file

Issue -But the obs count has increased from read 10 obs and writing it to 22 obs

Here the sample output:-

Your picture does not show anywhere near 22 obs. So how do you come to that conclusion.

Note: Variables may contain line feed, carriage return or vertical tab characters that when written to text appear to be new lines. Those are not "observations" but the side effect of how a program displaying text treats those characters.

Also if your line is long enough some of the programs displaying text will wrap the lines to fit a window or specified line length depending on program. Creating an illusion of more "obs".

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Kurt_Bremser — Wed, 13 Nov 2024 10:57:23 GMT

A text file does not have observations, only lines. "Observation" is used for a single record in a SAS dataset.

show the log
show the file

Copy/paste the text of both into a window opened with this button:

Note that you need to open the text file with a suitable text editor (e.g. Notepad++) for this.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Kurt_Bremser — Wed, 13 Nov 2024 11:01:41 GMT

Also note that some of your numbers are too big for the formats, causing asterisks or scientific notation to be written to the file.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Tom — Wed, 13 Nov 2024 12:51:50 GMT

Hard to tell what you are talking about. If you want to see what is in which column of the generated text file read it back in with SAS and use the LIST statement.

data _null_;
  infile"$currpath/NGRT.txt";
  input;
  list;
run;

If you want to show us what you find copy and paste the text from the SAS log.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Wed, 13 Nov 2024 13:08:37 GMT

Yes , In here there is only one obs ,the file is large so i cant share or show he file

When am I writing the Put statement as per Tom stated( this is for 100 variable)

I can process all the obs as you can see here

NOTE: 11 records were written to the file '$CURRPATH/YGRT010.txt'.
NOTE: There were 11 observations read from the data set WORKDIR.RPLPRNLN010.

But when its writing the obs into txt file

In the snippit the obs are not coming in the same line as it has been defined using put

instead the line are getting busted

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Wed, 13 Nov 2024 13:22:18 GMT

Yes , In here there is only one obs ,the file is large so i cant share or show he file

When am I writing the Put statement as per Tom stated( this is for 100 variable)

I can process all the obs as you can see here

NOTE: 11 records were written to the file '$CURRPATH/YGRT010.txt'.
NOTE: There were 11 observations read from the data set WORKDIR.RPLPRNLN010.

But when its writing the obs into txt file

In the snippit the obs are not coming in the same line as it has been defined using put

instead the line are getting busted

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Wed, 13 Nov 2024 13:33:41 GMT

This is the sample code for writing it to txt file all the carriage return and line feed are fixed

66 DATA _NULL_;
67 SET workdir.RPLPRNLN010;
68 FILE '$CURRPATH/YGRT010.txt' lrecl=2120 recfm=D;
69 put
70 @0001 cust_NB $16.
71 @0017 PCE_OFFR_DT 10.
72 @0027 PCE_OFFR_SQNC_NB 2.
73 @0029 PCE_ORIG_BRANCH_CD $2.
74 @0031 PCE_CO_BUS_CD 2.
75 @0033 PCE_ACCT_NB 11.
76 @0037 LAST_NM $40.
77 @0077 FIRST_NM $15.
78 @0092 MDDL_INTL_NM $4.
79 @0093 GNRT_NM $5.
80 @0098 BIRTH_DT $10.
81 @0108 ADDR_LINE1_TX $40.
82 @0148 ADDR_LINE2_TX $40.
83 @0188 CITY_NM $30.
84 @0218 ST_ABBR_CD $5.
85 @0223 ZIP_CD $11.
86 @0234 BNKRP_IN $1.
87 @0235 CMMRCL_IN $1.
88 @0236 ECR_REJ_IN $1.;
run;

alos there is ** is coming inthe output not sure why?

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

Tom — Wed, 13 Nov 2024 14:48:25 GMT

The asterisks will appear when you use formatted printing when the numeric value is invalid for the format specification used. For example there is no way to represent values larger than 99 with only two digits. So if you print 123 using 2. format you will get **. And you will get a note in the log about it. Example:

368  data _null_;
369    x=123;
370    put x 2.;
371  run;

**
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

In addition to the two notes you posted that show the number of observations read and lines written you should also see notes about how long the shortest and longest line was. Example:

345  data _null_;
346    file text;
347    set sashelp.class;
348    put name sex age ;
349  run;

NOTE: The file TEXT is:
      Filename=xxx,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=13Nov2024:09:19:56,
      Create Time=13Nov2024:09:19:56

NOTE: 19 records were written to the file TEXT.
      The minimum record length was 9.
      The maximum record length was 12.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.03 seconds

Notice

      The minimum record length was 9.
      The maximum record length was 12.

How are you LOOKING at the text file? Since you are writing lines that over 200 bytes long if you just type the file to a terminal the lines will wrap.

Did you try reading it with SAS? Again note that SAS will display in the log the min and max lengths of the lines read from the file in addition to the number of lines. Did SAS find 11 lines when it read the file.

There are only two ways a single PUT statement (without / or # cursor motion commands) could write more than one line. Either the text being written includes line breaks. In that case you will only notice the difference when you read back in the file.

Or the lines being written are longer than the record length being used by the FILE statement which will cause the PUT statement to move to a new line. But that case you will see a different number of lines written than observations read when checking the LOG from the data step that wrote the file.

363  data _null_;
364    file text lrecl=30;
365    set sashelp.class(obs=3);
366    put name sex @35 age ;
367  run;

NOTE: The file TEXT is:
      Filename=xxx,
      RECFM=V,LRECL=30,File Size (bytes)=0,
      Last Modified=13Nov2024:09:31:02,
      Create Time=13Nov2024:09:31:02

NOTE: 6 records were written to the file TEXT.
      The minimum record length was 2.
      The maximum record length was 9.
NOTE: There were 3 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

animesh123 — Wed, 13 Nov 2024 14:59:04 GMT

yep I am getting the same output ,but the MIN rec length and MAX is same
NOTE: 11 records were written to the file '$CURRPATH/YGRT010.txt'.
The minimum record length was 2119.
The maximum record length was 2119.
NOTE: There were 11 observations read from the data set WORKDIR.RPLPRNLN010_FINAL.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

Re: Reading data from SAS7BDAT FILE and converting it into txt file issue

ballardw — Wed, 13 Nov 2024 15:01:38 GMT

alos there is ** is coming inthe output not sure why?

If you provide a numeric format that is not wide enough to display the actual value SAS will display *. Generally will also attempt to use a BEST format but if the width you provided is too small still can't display.

An example:

data _null_;
   x=123456;
   file print;
   put "Format 2. result: " x = 2.;
   put "Format 4. result: " x = 4.;
   put "Format 6. result: " x = 6.;
   put "Format Best4. result: " x = Best4.;
run;

Which generates a result of:

                                                                                                  
Format 2. result: x=**                                                                            
Format 4. result: x=12E4                                                                          
Format 6. result: x=123456                                                                        
Format Best4. result: x=12E4

Also note that the LOG tells of this problem. My log from running the above code:

8    data _null_;
9       x=123456;
10      file print;
11      put "Format 2. result: " x = 2.;
12      put "Format 4. result: " x = 4.;
13      put "Format 6. result: " x = 6.;
14      put "Format Best4. result: " x = Best4.;
15   run;

NOTE: Non-portable document will be produced. The current settings of FORMCHAR use nonstandard
      line-drawing characters and the resulting output file will not render correctly unless all
      readers of the document have the SAS Monospace font installed. To make your document
      portable, issue the following command:
      OPTIONS FORMCHAR="|----|+|---+=|-/\<>*";

NOTE: 4 lines were written to file PRINT.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be
      shifted by the "BEST" format.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

The non-portable document bit is about the File Print destination.

The part that reflects your question is the second Note about the W.D format is too small. You cannot display a 6 digit integer with 2 positions. SAS shifted the 4. format to a BEST4 so it displays in scientific notation as best it can with 4 positions. SAS Log tells you this may be the result. Since a width of 2 does not have room for at least one digit of the number, the E, indicating exponent, and a power of 10 then all that can be displayed in two positions is **.

The exercise for the interested leader: What would actually display if a format of 3. was specified?

Character values just get cutoff at the character limit but since a display of possibly "12" instead of "123456" would be terribly misleading in terms of numeric values SAS doesn't do that.