topic Re: space trailing in SAS Procedures

space trailing

raveena — Tue, 16 Aug 2011 17:28:24 GMT

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

Re: space trailing

FrankHack — Tue, 16 Aug 2011 18:37:59 GMT

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;

Re: space trailing

data_null__ — Tue, 16 Aug 2011 19:55:15 GMT

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;

Re: space trailing

Ksharp — Wed, 17 Aug 2011 09:58:32 GMT

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

Re: space trailing

art297 — Thu, 18 Aug 2011 01:10:31 GMT

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.

Re: space trailing

raveena — Thu, 18 Aug 2011 15:36:26 GMT

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.

Re: space trailing

art297 — Thu, 18 Aug 2011 16:22:22 GMT

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;

Re: space trailing

raveena — Thu, 18 Aug 2011 19:48:22 GMT

Thanks Art297.. I got the exact output..