topic Re: Problem with exporting in pipe delimiter in SAS Procedures

Problem with exporting in pipe delimiter

Augusto — Fri, 28 Feb 2014 18:11:49 GMT

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.

Re: Problem with exporting in pipe delimiter

Reeza — Fri, 28 Feb 2014 18:17:52 GMT

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

Re: Problem with exporting in pipe delimiter

Augusto — Fri, 28 Feb 2014 18:27:36 GMT

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.

0|1|2|3

1|2

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

Re: Problem with exporting in pipe delimiter

PGStats — Fri, 28 Feb 2014 18:28:02 GMT

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;

Re: Problem with exporting in pipe delimiter

Reeza — Fri, 28 Feb 2014 18:36:18 GMT

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

Re: Problem with exporting in pipe delimiter

Tom — Fri, 28 Feb 2014 21:28:40 GMT

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_) (:);

run;

_N_=1

Alfred|M|69|112.5

Alfred|M||69|112.5

_N_=2

56.5|84

|||56.5|84

Re: Problem with exporting in pipe delimiter

ballardw — Fri, 28 Feb 2014 22:43:10 GMT

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)