BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Trishjais
Obsidian | Level 7

HI All,

I have a dataset ,which I need to export in pipe delimited format with headers and footers.

Headers and footers are a string basically. My problem is when I'm using dsd option, I am getting my headers and footers wrapped in quotation and without dsd the datarows with null / missing values are getting exported with a space.

1. situation example:

"FT|Supplier_name|13042020|1.0|0|"

ID1|ID2|ID3|||ID6| and so on

 

2. Situation example.

 

FT|Supplier_name|13042020|1.0|0|

ID1|ID2|ID3| | |ID6| and so on..

 

Please advise.

TIA

data _null_;
file report notitle delimiter='|';
length lbl $200;
lbl=cats('HD|Supplier_name|',put(today(),ddmmyyn8.),'|1.0|0|');
if _n_=1 then put lbl;
do until(eof);
set have end=eof;
put ID1-ID123;
end;
lbl=cats('FT|Suplier_name|',put(today(),ddmmyyn8.),'|',&obs.);
put lbl;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Example:

filename report temp;
data have;
  input id1-id3;
cards;
1 2 3
4 5 6
;

data _null_;
  file report dsd dlm='|';
  length lbl $200;
  if  _n_=1 then do;
     do lbl='HD','Supplier_name',put(today(),ddmmyyn8.),'1.0','0';
         put lbl @;
     end;
     put;
  end;
  if eof then do;
    _n_=_n_-1;
    do lbl='FT','Supplier_name',put(today(),ddmmyyn8.);
       put lbl @;
     end;
     put _n_ ;
   end;
   set have end=eof;
   put ID1-ID3;
run;

Results:

1335  data _null_;
1336    infile report;
1337    input;
1338    put _infile_;
1339  run;

NOTE: The infile REPORT is:

      Filename=...\#LN00056,
      RECFM=V,LRECL=32767,File Size (bytes)=76,
      Last Modified=13Apr2020:15:44:09,
      Create Time=13Apr2020:15:44:09

HD|Supplier_name|13042020|1.0|0
1|2|3
4|5|6
FT|Supplier_name|13042020|2
NOTE: 4 records were read from the infile REPORT.
      The minimum record length was 5.
      The maximum record length was 31.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

View solution in original post

5 REPLIES 5
data_null__
Jade | Level 19

Add DSD option on the FILE statement.

Trishjais
Obsidian | Level 7

Hello,

Thanks for replying.

Actually I have tried with dsd option, see situation 1. Its  double quoting my header and footer string.

Tom
Super User Tom
Super User

@Trishjais wrote:

Hello,

Thanks for replying.

Actually I have tried with dsd option, see situation 1. Its  double quoting my header and footer string.


Write the header one value at a time. Don't try to write all of the headers as if they were one string.

Tom
Super User Tom
Super User

Example:

filename report temp;
data have;
  input id1-id3;
cards;
1 2 3
4 5 6
;

data _null_;
  file report dsd dlm='|';
  length lbl $200;
  if  _n_=1 then do;
     do lbl='HD','Supplier_name',put(today(),ddmmyyn8.),'1.0','0';
         put lbl @;
     end;
     put;
  end;
  if eof then do;
    _n_=_n_-1;
    do lbl='FT','Supplier_name',put(today(),ddmmyyn8.);
       put lbl @;
     end;
     put _n_ ;
   end;
   set have end=eof;
   put ID1-ID3;
run;

Results:

1335  data _null_;
1336    infile report;
1337    input;
1338    put _infile_;
1339  run;

NOTE: The infile REPORT is:

      Filename=...\#LN00056,
      RECFM=V,LRECL=32767,File Size (bytes)=76,
      Last Modified=13Apr2020:15:44:09,
      Create Time=13Apr2020:15:44:09

HD|Supplier_name|13042020|1.0|0
1|2|3
4|5|6
FT|Supplier_name|13042020|2
NOTE: 4 records were read from the infile REPORT.
      The minimum record length was 5.
      The maximum record length was 31.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Trishjais
Obsidian | Level 7
Thanks Tom, That worked like a charm. didn' t know that one header value a a time would do this.
Much appreciated.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 1174 views
  • 0 likes
  • 3 in conversation