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

[animesh123]

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 

[Kurt_Bremser]

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!

[Patrick]

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.

[ballardw]

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.

[Tom]

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.

[animesh123]

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:

-

[LinusH]

Show your code and log for this execution.

[ballardw]

---

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

[animesh123]

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 

[Kurt_Bremser]

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.

[Kurt_Bremser]

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

[Tom]

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.  

[animesh123]

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

[animesh123]

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?

[ballardw]

---

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.