BookmarkSubscribeRSS Feed
raveena
Obsidian | Level 7

Hi ,

I have an dataset with multiple columns, some of the columns are derived fields with no values.I need to export an data to the text file with delimited tilde , in that need to get an spaces between two '~'.

Example :

data test;
input id  name $  ;
datalines;
1 bob
2 dave
3 cari
4 laura
5 sunny
;
run;

Adding extra variables to the table:

proc sql;
alter table test
add prov_localty char, prov_spec char;
quit;


proc sql;
create table test1 as
select id as id_num,
       prov_localty,
    prov_spec,
    name from test;
    quit;

proc export data=work.test1
outfile="C:\Documents and Settings\Desktop\test.dat"
DBMS=DLM Replace;
DELIMITER= '~';
RUN;

output looks like this when exporting an data:
id_num~prov_localty~prov_spec~name
1~~~bob
2~~~dave
3~~~cari
4~~~laura
5~~~sunny

Need to get an outlike look like this:

id_num~prov_localty~prov_spec~name
1~ ~ ~bob
2~ ~ ~dave
3~ ~ ~cari
4~ ~ ~laura
5~ ~ ~sunny

Thanks in advance

7 REPLIES 7
FrankHack
Calcite | Level 5

Greetings,

Try replacing the proc export step with a data _null_ step similar to the following.

data _null_;

file "C:\Documents and Settings\raveena\Desktop\test.dat" delimiter = '~';

set test1;

put id_num prov_localty prov_spec name;

run;

data_null__
Jade | Level 19

The action you are looking for is given by DLM= in the absence of option DSD.  You can use an Old-Style Macro to alter the code generated by PROC EXPORT.  Be sure to %DEACT the macro when you are done.  In line 429 below the old style macro defines the string DSD to be blank.  So anytime SAS "sees" that string it replaces the string with the value of the macro, in the case blank.  Therefore the DSD INFILE statement option in line 447 is "removed".

427  options macrogen=1;

428

429  macro dsd %

430

431  proc export data=work.test1

432     outfile="test0910.dat"

433     DBMS=DLM Replace;

434     DELIMITER= '~';

435     RUN;

436   /**********************************************************************

437   *   PRODUCT:   SAS

438   *   VERSION:   9.2

439   *   CREATOR:   External File Interface

440   *   DATE:      16AUG11

441   *   DESC:      Generated SAS Datastep Code

442   *   TEMPLATE SOURCE:  (None Specified.)

443   ***********************************************************************/

444      data _null_;

445      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

446      %let _EFIREC_ = 0;     /* clear export record count macro variable */

447      file 'test0910.dat' delimiter='~' DSD DROPOVER lrecl=32767;

NOTE: The old-style macro DSD is beginning resolution.

448 +

NOTE: The old-style macro DSD is ending resolution.

449      if _n_ = 1 then        /* write column names or labels */

450       do;

451         put

452            "id_num"

453         '~'

454            "prov_localty"

455         '~'

456            "prov_spec"

457         '~'

458            "name"

459         ;

460       end;

461     set  WORK.TEST1   end=EFIEOD;

462         format id_num best12. ;

463         format prov_localty $8. ;

464         format prov_spec $8. ;

465         format name $8. ;

466       do;

467         EFIOUT + 1;

468         put id_num @;

469         put prov_localty $ @;

470         put prov_spec $ @;

471         put name $ ;

472         ;

473       end;

474      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

475      if EFIEOD then call symputx('_EFIREC_',EFIOUT);

476      run;

NOTE: The file 'test0910.dat' is:

      (system-specific pathname),

      (system-specific file attributes)

NOTE: 6 records were written to the file (system-specific pathname).

      The minimum record length was 9.

      The maximum record length was 34.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.01 seconds

5 records created in test0910.dat from WORK.TEST1.

NOTE: "test0910.dat" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.07 seconds

      cpu time            0.06 seconds

477

478  %deact dsd;

Ksharp
Super User

You can copy code generated by proc export from log,and tailor it (i.e. add dsd or something else)

proc export looks like cannot do it. But if you converse missing character variable into missing numeric variable,

then it could be (use "options missing= ' '  ")

Ksharp

art297
Opal | Level 21

DN,

Fantastic find!  I don't understand why this question hasn't been indicated as having been answered.  In case anyone missed the code through DN's excellent explanation of it:

macro dsd %

proc export data=work.test1

   outfile="test0910.dat"

   DBMS=DLM Replace;

   DELIMITER= '~';

RUN;

%deact dsd;

does exactly what Raveena had requested.

raveena
Obsidian | Level 7

Hi ,

Is there anyway to insert/force white space only for particular two columns in exporting data to a delimited text file.

For example:

Current output looks like:

7183384855~~~~IP~~~P~~~15~081448039~~19420507~000~N~~~-0~-0

Need an output as:

7183384855~~~~IP~~~P~~~15~081448039~~19420507~000~N~             ~          ~-0~-0

Thanks in Advance.

art297
Opal | Level 21

If you modify the code that proc export produces, you can pretty much do anything you want.  The following code is your original example, but including a modified version of the code that proc export had produced, the modifications simply being the addition of white space where desired:

data test;

input id  name $  ;

datalines;

1 bob

2 dave

3 cari

4 laura

5 sunny

;

run;

proc sql;

  alter table test

  add prov_dummy1 char, prov_dummy2 char,

      prov_dummy3 char,prov_localty char,

      prov_spec char;

quit;

 

proc sql;

create table test1 as

select id as id_num,

    prov_dummy1,

    prov_dummy2,

    prov_dummy3,

    prov_localty,

    prov_spec,

    name from test;

    quit;

data _null_;

file 'c:\art\test0910.dat' delimiter='~' dsd lrecl=32767;

if _n_ = 1 then do;

    put

       "id_num"

    '~'

       "prov_dummy1"

    '~'

       "prov_dummy2"

    '~'

       "prov_dummy3"

    '~'

       "prov_localty"

    '~'

       "prov_spec"

    '~'

       "name"

    ;

  end;

  set  WORK.TEST1;

  format id_num best12. ;

  format prov_dummy1 $8. ;

  format prov_dummy2 $8. ;

  format prov_dummy3 $8. ;

  format prov_localty $8. ;

  format prov_spec $8. ;

  format name $8. ;

  put id_num @;

  put prov_dummy1 $ @;

  put prov_dummy2 $ @;

  put " "@;

  put prov_dummy3 $ @;

  put " "@;

  put prov_localty $ @;

  put prov_spec $ @;

  put name $ ;

run;

raveena
Obsidian | Level 7

Thanks Art297.. I got the exact output..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 879 views
  • 3 likes
  • 5 in conversation