Proc Export - Issues with Exporting to .txt files

Reply
Frequent Contributor
Posts: 121

Proc Export - Issues with Exporting to .txt files

Below is the code that I have been using; attached is also the dataset to which I apply the code. My problem is in the proc export step. I would like to have my .txt file look like dataset NCDebt7 off the code below, whereby there are no blank lines between records, except for that one line after each 'end' statement. Is there anyway that I can accomplish this?

Any advice is welcome.

data WORK.NCDebt;
     infile '/fmacdata/utility/fin/KBenchmarks/SAS Data/NCDebt.csv' dsd lrecl=40000 firstobs=3;
informat InstrumentName $15.;
informat PriceToPublic 10.8;
informat Cusip $15.;
informat CouponDates mmddyy10.;
informat ValuationDate yymmdd.;
informat AccruedDayCount $10.;
informat FullPrice 10.8;
informat InterestFrequency 5.;
informat Notional 12.;
informat MaturityDate mmddyy10.;
informat ID $10.;
informat FaceValues 8.;
informat PaymentDayCount $10.;
informat FlatPrice 10.8;
informat Description $30.;
informat Comments $30.;
informat MarketCleanPrice 10.8;
informat SettlementDate yymmdd.;
informat Name $20.;
informat Category $40.;
informat PaymentDates mmddyy10.;
informat AnnualCouponRates 6.4;
informat OriginationDate mmddyy10.;
informat OAS 5.2;
informat FirstCpnDt mmddyy10.;
format PriceToPublic 10.8;
format FullPrice 10.8;
format CouponDates MaturityDate ValuationDate PaymentDates OriginationDate
       SettlementDate FirstCpnDt date9.;
input InstrumentName $ PriceToPublic Cusip $ CouponDates ValuationDate
      AccruedDayCount $ FullPrice InterestFrequency Notional MaturityDate
      ID $ FaceValues PaymentDayCount $ FlatPrice Description $ Comments $
      MarketCleanPrice  SettlementDate Name $ Category $ PaymentDates 
      AnnualCouponRates OriginationDate OAS FirstCpnDt;
ID=Cusip;
Name=Cusip;
run;

data NCDebt2;
  retain InstrumentName PriceToPublic Cusip CouponDates ValuationDate
         AccruedDayCount DiscountSource FullPrice UnderWriterDelivery
         InterestFrequency Notional MaturityDate ID FaceValues
         PaymentDayCount CouponAdjusting FlatPrice Description Comments
         MarketCleanPrice BusinessDayAdjust CouponEntry SettlementDate Name Category
         PaymentDates AnnualCouponRates UnderwriterDiscount DisableLegacyMeasures
         OriginationDate OAS FirstCpnDt;
         DiscountSource='OIS';
         UnderWriterDelivery=-1;
         CouponAdjusting='false';
         BusinessDayAdjust='Following';
         CouponEntry='Manual';
  UnderwriterDiscount=0.0;
         DisableLegacyMeasures='false';
         format UnderwriterDiscount 3.1;
         set NCDebt;
run;


%let dsid=%sysfunc(open(NCDebt2));
%let Nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%macro test();

%do i=1 %to %eval(&Nobs);

data NCDebt3;
   set NCDebt2(firstobs=&i obs=&i);
   if InterestFrequency=2  then interval='semiyear';
   if InterestFrequency=12 then interval='month   ';
   intervals= intck(interval,FirstCpnDt,MaturityDate);
   do i=1 to intervals;
     CouponDates=intnx(interval,MaturityDate,i-intervals,'same');
     PaymentDates=CouponDates;
     output;
     format CouponDates PaymentDates date9.;
   end;
run;

data NCDebt4;
   set NCDebt3(obs=1);
   CouponDates=FirstCpnDt;
   PaymentDates=FirstCpnDt;
run;

proc append base=NCDebt4
            data=NCDebt3;
run;

proc transpose data=NCDebt4 out=NCDebt5(rename=(_name_=var1));
var InstrumentName PriceToPublic Cusip CouponDates ValuationDate
     AccruedDayCount DiscountSource FullPrice UnderWriterDelivery
     InterestFrequency Notional MaturityDate ID FaceValues
     PaymentDayCount CouponAdjusting FlatPrice Description Comments
     MarketCleanPrice BusinessDayAdjust CouponEntry SettlementDate
     Name Category PaymentDates AnnualCouponRates UnderwriterDiscount
     DisableLegacyMeasures OriginationDate OAS;
run;

%let dsid=%sysfunc(open(NCDebt5));
%let n=%sysfunc(attrn(&dsid,nvars));
%let dsid=%sysfunc(close(&dsid));
%let NCol=%eval(&n-1);

data End;
array COL{&NCol} $ COL1-COL&NCol;
do i=1 to dim(COL);
  COL(i)='end';
end;
drop i;
run;

data Blank;
array BLANK{&NCol} $ BLANK1-BLANK&NCol;
do i=1 to dim(BLANK);
  BLANK(i)=' ';
end;
drop i;
run;

proc datasets library=work;
     append base=NCDebt5 data=End FORCE;
     append base=NCDebt5 data=Blank FORCE;
quit;

%macro blanks();
  %do k=2 %to %eval(&NCol);
     COL%eval(&k)=' ';
  %end;
%mend blanks;

data NCDebt6;
    set NCDebt5;
     if var1 not in ('CouponDates','FaceValues','PaymentDates','AnnualCouponRates') then do;
       %blanks();
     end;
run;

proc append
     base=NCDebt7 data=NCDebt6 FORCE;
run;

%end;

%mend;

%test();

proc export data=NCDebt7
outfile="/fmacdata/utility/fin/KBenchmarks/SAS Data/NCDebt.txt"
dbms=tab
replace;
putnames=no;
run;


Attachment
Super User
Posts: 17,801

Re: Proc Export - Issues with Exporting to .txt files

Use a data _null_ with PUT statements instead of Proc Export.

Super User
Super User
Posts: 6,499

Re: Proc Export - Issues with Exporting to .txt files

So it really looks like you are trying to read in a file and produce a report.

The input file is almost structured like a data file, but some of the variable names (column headers) are missing.

The output file looks much more like a REPORT since the column contain mixed data types.

Hence there is not really any advantage in creating a data set that looks like your final report, since it is NOT a data set.

So the problem is much easier as it is then just a reporting step to write the report.

We could clean up your input step a fair amount such as eliminating all of the data steps and just put all of the calculations and constant values into the first step.

%let path=C:\Downloads;

%let infile=&path\NCDebt.csv;

%let outfile=&path\NCDebt.txt;

data STEP1;

  infile "&infile" dsd lrecl=40000 firstobs=3;

  length

    InstrumentName $15

    PriceToPublic 8

    Cusip $15

    CouponDates 8

    ValuationDate 8

    AccruedDayCount $10

    FullPrice 8

    InterestFrequency 8

    Notional 8

    MaturityDate 8

    ID $10.

    FaceValues 8

    PaymentDayCount $10

    FlatPrice 8

    Description $30

    Comments $30

    MarketCleanPrice 8 

    SettlementDate 8

    Name $20

    Category $40

    PaymentDates 8

    AnnualCouponRates 8

    OriginationDate 8

    OAS 8

    FirstCpnDt 8

;

informat

  CouponDates MaturityDate PaymentDates OriginationDate FirstCpnDt mmddyy10.

  ValuationDate SettlementDate yymmdd10.

;

format PriceToPublic FullPrice 10.8

       CouponDates MaturityDate ValuationDate PaymentDates OriginationDate

       SettlementDate FirstCpnDt yymmdd10.

;

input InstrumentName -- FirstCpnDt;

ID=Cusip;

Name=Cusip;

DiscountSource='OIS';

UnderWriterDelivery=-1;

CouponAdjusting='false';

BusinessDayAdjust='Following';

CouponEntry='Manual';

UnderwriterDiscount=0.0;

DisableLegacyMeasures='false';

format UnderwriterDiscount 3.1;

if InterestFrequency=2  then interval='semiyear';

else if InterestFrequency=12 then interval='month   ';

intervals= intck(interval,FirstCpnDt,MaturityDate);

run;


Since you seem to want column headers in your report we need to find the MAX() value of INTERVALS so that we now the maximum number of columns to write.  If you do not want the column headers in the report then you can skip this and simplify the step that writes the report.

proc sql noprint ;

  select max(intervals)+1 into :lastcol separated by ' '

  from step1

  ;

quit;

Now we are ready to write the tab delimited report file.  I am not sure how you determined the row order in the report, but one way would be to make a list of the variable names.

%let varlist=

InstrumentName

PriceToPublic

Cusip

CouponDates

ValuationDate

AccruedDayCount

DiscountSource

FullPrice

UnderWriterDelivery

InterestFrequency

Notional

MaturityDate

ID

FaceValues

PaymentDayCount

CouponAdjusting

FlatPrice

Description

Comments

MarketCleanPrice

BusinessDayAdjust

CouponEntry

SettlementDate

Name

Category

PaymentDates

AnnualCouponRates

UnderwriterDiscount

DisableLegacyMeasures

OriginationDate

OAS

;

You can then scan over those names and use the VVALUEX() function to get the value to display in the second column.  When you hit one of the variable where you want to write a string of dates or values you can just write them out as you are generation the report.

data _null_ ;

  file "&outfile" dsd dlm='09'x lrecl=30000 ;

  length varname $32 value $50 ;

  if _n_=1 then do;

     varname='VARIABLE';

     value='VALUE';

     put varname value @ ;

     do i=2 to &lastcol;

        put 'COL' i @;

     end;

     put;

     length varlist $500 ;

     varlist=compbl(symget('varlist'));

     nvars=countw(varlist);

     retain varlist nvars;

  end;

  set step1;

  do i=1 to nvars;

    varname=scan(varlist,i);

    value=vvaluex(varname);

    put varname value @;

    if varname in ('CouponDates','PaymentDates') then do j=1 to intervals;

value=put(intnx(interval,MaturityDate,j-intervals,'same'),yymmdd10.);

       put value @;

    end;

    if varname in ('AnnualCouponRates','FaceValues') then do j=1 to intervals;

       put value @;

    end;

    put ;

  end;

  varname = ' ';

  value='end';

  put varname value;

run;

I am not sure what you are reading the report file with.  If you open it in EXCEL it will look really ugly.  But we can read it back into SAS and make a check dump of it if we just treat each value as a character string.

data check ;

  infile "&outfile" firstobs=2 dsd dlm='09'x truncover lrecl=30000 ;

  length variable value col2-col&lastcol $50 ;

  input variable -- col&lastcol ;

run;

proc print ;

run;

Ask a Question
Discussion stats
  • 2 replies
  • 205 views
  • 0 likes
  • 3 in conversation