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
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;
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;
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
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.
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.
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;
Thanks Art297.. I got the exact output..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.