Help using Base SAS procedures

Problem with exporting in pipe delimiter

Reply
Frequent Contributor
Posts: 80

Problem with exporting in pipe delimiter

Hello all,

Firstly, thanks for the helps.

I have a problem to export a file in pipe delimiter, I explan.

I would like to export a file with pipe delimiter like this.

834.728.000-20|MARLI RAMOS DOS SANTOS|RUA JASMIM, 640|CENTRO|CORBELIA|PR|85420000|640||60-092626/10310

834.734.000-91|SONIA MARIA FERREIRA DOS SANTOS|RUA DA LARANJEIRA, SN|BAIXA GRANDE|INAMBUPE|BA|48490000|SN|CASA|52-474573/11310

834.735.000-91|MARIA JOSE DA SILVA|RUA DANIEL DA CRUZ, 60|CHA DA TABUA|SAO LOURENCO DA MATA|PE|54745145|60|CASA|22-681662/12310

834.736.000-04|SEVERINA MARIA DOS SANTOS DA SILVA|RUA: 21 DE JUNHO, 220|DOIS  UNIDOS|RECIFE|PE|52140000|220|CASA|61-098854/10002

but my result is:

000.000.847-86|DIK FARNEY SIQUEIRA|RUA TTE NILDO BATISTA, 62|S PAULO|NAVEGANTES|SC|88375000|62||57-212858/09310||||||||||||||||||||||||

000.001.515-64|IRENE MARIA DA SILVA|LOT 5 SAO GERALDO, 05|QD B COND M DAS ARVORES|JUAZEIRO|BA|48905650|05|A|57-325064/10310|22-725705/13310|88-262129/12310|57-474799/11310|22-640519/12310||||||||||||||||||||

000.001.627-60|IARA TANIA GONCALVES|AVE RUI BARBOSA, 295|SAO FRANCISCO|NITEROI|RJ|24000000|295|?P201|36-110900/12314|36-114929/13314|36-117092/13314||||||||||||||||||||||

000.002.007-94|SOLANGE DE AZEVEDO MARINHO|ESTRADA AUSTRIA, 240|RIO DO OURO|SAO GONCALO|RJ|24753470|240||57-532026/12310||||||||||||||||||||||||

So would like to not export pipes for missing values.

Can anyone help me?

Thanks in advance.

Super User
Posts: 19,815

Re: Problem with exporting in pipe delimiter

How will the person reading the data know that data is missing? 

Frequent Contributor
Posts: 80

Re: Problem with exporting in pipe delimiter

Thanks Reeza for taking time to help me. Welll, i think i had not explan my problem clearly,

I my table there are datas filled and not filled.

ex.

a b c d

.  .  .  2

0 1 2 3

1 .  .  .

.  1.   2

when i export using pipe i get this result.

|||2

0|1|2|3

1|||

|1||2

but i would like this result.

2

0|1|2|3

1|

1|2

So i do not need the blanks (missing) values exported with pipe.

Super User
Posts: 19,815

Re: Problem with exporting in pipe delimiter

But how would someone reading the data in, know that 2 belongs to variable D and not A,B, or C?

Respected Advisor
Posts: 4,925

Re: Problem with exporting in pipe delimiter

Hi,

if you want to eliminate all missing values from your output file, then you could do:

data _null_;

set myDataset;

length myStr $256;

myStr = catx("|", of _all_);

file "myPath\myFile.csv";

put myStr;

run;

PG

PG
Super User
Super User
Posts: 7,050

Re: Problem with exporting in pipe delimiter

That format is going to be unreadable.

One way is to generate a series of statements like:

if not missing(XXX) then put XXX @ ;

So here is an example using SASHELP.CLASS of one way to generate that series using PROC SQL to put the if statements into a variable.  Will only work for a small number of variables because there is a limit to how many characters you can put into a single macro variable.

proc contents data=sashelp.class noprint out=contents ;run;

proc sql noprint ;

select catx(' ','if not missing(',name,') then put',name, '@')

   into :code separated by ';'

from contents

order by varnum

;

quit;

So here is an example data step where I use these generated put statements to write what you want and then also write below it what SAS would normally write.

data _null_;

file log dsd dlm='|' ;

set sashelp.class (obs=2);

if _n_=1 then age=.;

if _n_=2 then call missing(name,age,sex);

put _n_=1 ;

&code ;

put ;

put (_all_) (Smiley Happy;

run;

_N_=1

Alfred|M|69|112.5

Alfred|M||69|112.5

_N_=2

56.5|84

|||56.5|84

Super User
Posts: 11,343

Re: Problem with exporting in pipe delimiter

How are you exporting? Proc Export, data step or something else.

If using Proc export you could restrict the data fields exported to just the ones you want insted of the entire dataset if that is what you have been doing.

Proc Export data=mydataset (keep=var1 var2 var 3)

<rest of code here>

Ask a Question
Discussion stats
  • 6 replies
  • 349 views
  • 0 likes
  • 5 in conversation