DATA Step, Macro, Functions and more

Exporting a sas data set into .txt

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Exporting a sas data set into .txt

[ Edited ]

hello experts

I want To export a sas data set into .txt file 

input : data set

 

id   name

12   bob

 

i want to use sas 9.2 and  get result.txt likes this 

id;Name

"12";"bob"

thank you

 

 

 

 


Accepted Solutions
Solution
‎11-25-2016 01:26 PM
Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

[ Edited ]

I still want to know WHY you need to insert quotes where they are not needed.

 

It also looks like you want to append an extra semi-colon to every line.

Here is a simple data step you can use that should work for any input data step.  Just update the FILE statement to write the data to a file instead of the log.  

 

First let's make a small sample dataset with some missing values so we have something to test with.

data test;
  set sashelp.class (obs=4 keep=name sex age );
  if _n_=2 then call missing(sex);
  if _n_=3 then call missing(age);
run;

So this data step will output semi-colon delimited data.  Basically it uses CALL VNEXT() to get the variable names in order. Then it used VVALUEX() function to get the formatted value of the variable which it quotes using the QUOTE() function.  It then using the $VARYING format to output the value without extra spaces and adds a trailing semi-colon.

 

To make the output step coding simplier I had it pull in one extra observation at the front that will be used to output the column headers.  

 

data _null_;
  file log ;
  set test(obs=1) test;
  length __name $32 __length 8 __value $200 ;
  do while (1=1);
    call vnext(__name);
    if lowcase(__name)='__name' then leave;
    if _n_=1 then __value = __name ;
    else __value = quote(strip(vvaluex(__name)));
    __length=lengthn(__value);
    put __value $varying200. __length ';' @ ;
  end;
  put;
run;

Here is the resulting output for this test data. Note that it will output two adjacent double quotes ('""') when a character value is missing. If you want it to do the same for numeric variables then change the MISSING option to a space. ( options missing=' '; ) before running the data step.

 

Name;Sex;Age;
"Alfred";"M";"14";
"Alice";"";"13";
"Barbara";"F";"";
"Carol";"F";"14";

 

You could also increase the length of the __VALUE variable and the corresponding $VARYINGxxx. format in the PUT statement if your quoted values are longer than 200 characters.

 

Another point you might want to check is how does your target system want you to handle data that contains double quote characters? The QUOTE() function will double up embedded quotes.  Some systems use an escape character like '\' instead.  If you need that then you should replace 

__value = quote(strip(vvaluex(__name)));

with 

__value = cats('"',tranwrd(vvaluex(__name),'"','\"'),'"');

 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: exportinng sas data set into .txt

Super Contributor
Posts: 371

Re: exportinng sas data set into .txt

@Reeza: Thank you

The solution of @Ksharp,

It seems to be quiet well, is it working for SAS 9.2 ?

 

ods listing close;
ods csv file='c:\temp\y.txt' options(doc='help' Delimiter=';');
proc print data=have label noobs;run;
ods csv close;
ods listing;

 

Super User
Posts: 19,770

Re: exportinng sas data set into .txt

Have you tried it and it didn't work? That would be the fastest method.

 

I doubt it would honestly, I don't think ODS CSV was available them. 

The Solution from Haiku is your best bet.

Super User
Posts: 19,770

Re: exportinng sas data set into .txt

Super Contributor
Posts: 371

Re: exportinng sas data set into .txt

[ Edited ]

@Reeza : Thank you

But, I do not see in the options     

Delimiter=

    

Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

I am curious as to why the quotes are needed?  Normally in a delimited file quotes are only needed if the value contains the delimiter or the quote character.

 

Also does it need the quotes for all values? Or does it only add the quotes when the variable is supposed to be character instead of numeric, sort of as a trick to embed data type into the format of the delimited file?

 

Are you reading the file with some software that needs the quotes? If so what software?

 

Super Contributor
Posts: 371

Re: Exporting a sas data set into .txt

hi Tom
i need thé double quote for Alm variables : both Numéris and character ..but ont in thé name of variable.
id;name;
"12";"bob";
tank you
Solution
‎11-25-2016 01:26 PM
Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

[ Edited ]

I still want to know WHY you need to insert quotes where they are not needed.

 

It also looks like you want to append an extra semi-colon to every line.

Here is a simple data step you can use that should work for any input data step.  Just update the FILE statement to write the data to a file instead of the log.  

 

First let's make a small sample dataset with some missing values so we have something to test with.

data test;
  set sashelp.class (obs=4 keep=name sex age );
  if _n_=2 then call missing(sex);
  if _n_=3 then call missing(age);
run;

So this data step will output semi-colon delimited data.  Basically it uses CALL VNEXT() to get the variable names in order. Then it used VVALUEX() function to get the formatted value of the variable which it quotes using the QUOTE() function.  It then using the $VARYING format to output the value without extra spaces and adds a trailing semi-colon.

 

To make the output step coding simplier I had it pull in one extra observation at the front that will be used to output the column headers.  

 

data _null_;
  file log ;
  set test(obs=1) test;
  length __name $32 __length 8 __value $200 ;
  do while (1=1);
    call vnext(__name);
    if lowcase(__name)='__name' then leave;
    if _n_=1 then __value = __name ;
    else __value = quote(strip(vvaluex(__name)));
    __length=lengthn(__value);
    put __value $varying200. __length ';' @ ;
  end;
  put;
run;

Here is the resulting output for this test data. Note that it will output two adjacent double quotes ('""') when a character value is missing. If you want it to do the same for numeric variables then change the MISSING option to a space. ( options missing=' '; ) before running the data step.

 

Name;Sex;Age;
"Alfred";"M";"14";
"Alice";"";"13";
"Barbara";"F";"";
"Carol";"F";"14";

 

You could also increase the length of the __VALUE variable and the corresponding $VARYINGxxx. format in the PUT statement if your quoted values are longer than 200 characters.

 

Another point you might want to check is how does your target system want you to handle data that contains double quote characters? The QUOTE() function will double up embedded quotes.  Some systems use an escape character like '\' instead.  If you need that then you should replace 

__value = quote(strip(vvaluex(__name)));

with 

__value = cats('"',tranwrd(vvaluex(__name),'"','\"'),'"');

 

Super Contributor
Posts: 371

Re: Exporting a sas data set into .txt

@Tom thank you so much for your answer that s really that what i want..

To reply To your question about  why the quote?

because this file Will ne the in put of an o ther software is not sas ...

please, why do you have used do while (1=1)

and test (obs=1)

 

Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

[ Edited ]

But WHAT is this software that requires such a strange file format?  Is it something home grown?  Can you fix it? If it is some software that a lot of people use perhaps we can shame them into making it accept more standard file formats.

 

The WHILE (1=1) is to setup an infinite loop. The LEAVE statement will terminate the loop.

 

The TEST(obs=1) is to get the extra observation, as I explaned in the earlier post. Having the extra observations means that it can output the names of the variables the first time that the data step loop runs without adding a lot of extra code.  You would replace TEST with the actual table you are printing.  Actually I could make it easier by just using some other table that you know always exists like SASHELP.CLASS and drop all of the variable.

 

set sashelp.class(obs=1 drop=_all_) TEST ;

Then you could replace TEST with something that already includes other dataset options.

 

%let mydata= result_table(where=(state='NY')) ;
...
set sashelp.class(obs=1 drop=_all_) &mydata ;
..

 

Also then if your input dataset was empty you would still get a text file with the variable names.

 

Super Contributor
Posts: 371

Re: Exporting a sas data set into .txt

[ Edited ]

Hello Tom,

Thank you again for your answers.

To answer, my aim was only to product this file with this format ...

 Is it something home grown?  Can you fix it?  ... I do not know

 

Please, I need the explications about the use of "set" here and  "it can output the names of the variables the first time that the data step loop runs without adding a lot of extra code."

If  Iunderstand well, your program will application the algorithm on the first variable, the second, so on, until

if lowcase(__name)='__name' then leave;

 

Why this "set" is different from

data t1;

v=2;

run;

 

data t2;

v=3;

run;

 

data final;

set t1 t2;

run;

 

proc print data=final noobs; run;

 

v

2

3

 

 

Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

It is really no different than setting two different datasets as you posted. But since it is using the OBS=1 dataset option on the first one it will only read one observation from that dataset. Then lower down in the program you can see that it uses IF _N_=1 to do somthing different when it is operating on that first observation. Specificaly for the first observation it outputs the name of the varible instead of the quoted formatted value of the variable.

 

The reason the DO loop works is that CALL VNEXT() returns the variable names in the order that they are defined. The __NAME variable is the first one defined after those found in the dataset(s) referenced in the SET statement. That is why it stops the loop when it sees __NAME.

Super User
Super User
Posts: 7,039

Re: Exporting a sas data set into .txt

[ Edited ]

Here is a version that adds an extra DO loop instead of pulling in an extra observation.

 

data _null_;
  file log ;
  set test ;
  length __name $200 ;
  do _n_=_n_-(_n_=1) to _n_;
    do until (__name=' ');
      call vnext(__name);
      if lowcase(__name) in ('__name','_error_','_n_',' ') then continue;
      if _n_>0 then __name = quote(strip(vvaluex(__name)));
      put __name +(-1) ';' @ ;
    end;
    put;
  end;
run;

I simplified the code by eliminating the __LENGTH and __VALUE variables and instead just used the __NAME variable and a pointer movement command in the PUT statement to overwrite the extra space that PUT generates after a variable with the semi-colon that it was going to write anyway.

 

To make it possible to run through the list of variable names using CALL VNEXT() twice on the first observation I needed to change the main DO loop so that it will stop after it pulls all of the names. That way on the next iteration of the new outer DO loop it will start from the beginning again. That wasn't needed before because CALL VNEXT() always starts over when you are on the next iteration of the data step.  So instead using LEAVE it uses UNTIL (__name=' ') to end the DO loop and uses CONTINUE to skip the names that we don't want to output.  CALL VNEXT() always adds '_ERROR_', '_N_' and ' ' to the end of the set of results it returns, in that order.

 

The new outer DO loop will run twice for the first observation and only once for all of the other observations.  This is because SAS will evaluate the logical expression (_n_=1) to 0 or 1 depending on if it is false or trun.  

 

By using _N_ as the index variable I do not need to introduce another new variable whose name might conflict with a variable name that is already on the dataset.

Super Contributor
Posts: 371

Re: Exporting a sas data set into .txt

[ Edited ]

@Tom : Thank you very much for your detailed explanation. Really, it is a wonderful solution. That's very kind from you.

Please, I want to understand some thing in your solution about the new  variable  __name

in one hand, and by the instruction

length __name $200 ;

 We will create a new variable __name is initialzed to blank( =' ')

 

On the other hand, by the instruction

if _n_>0 then __name = quote(strip(vvaluex(__name)));

We will get  this results

Name;Sex;Age;
"Alfred";"M";"14";

So, please, how can this new variable __name is initialized to "blank" and gives the réésult is not blank ?

Is there a new initialion for the new variable __name by the instruction ?

 

 call vnext(__name);

in the first time, the variable __name became Name, in the second time Sex, in the third time  Age    ?

 

At the end thank you again for your answers.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 491 views
  • 6 likes
  • 3 in conversation