CODEWITHSUNDEEP
sas
useR
useR

Reputation: 3082

file statement in data step to export comma delimited text file

Problem: suppose i do not know the variable name and number of variable. or imagine there are too many variables that i cannot write the put statement.

the following cases is that i knew there are 3 varialbes

data _null_;
    set meeting;
    file 'C:\Users\Desktop\meeting2.txt' dlm=',';
    put region mtg sendmail;
run;

I tried using put _all_; And the output is:

region=N mtg=24NOV1999 sendmail=10OCT1999 _ERROR_=0 _N_=1
region=S mtg=28DEC1999 sendmail=13NOV1999 _ERROR_=0 _N_=2
region=E mtg=03DEC1999 sendmail=19OCT1999 _ERROR_=0 _N_=3
region=W mtg=04OCT1999 sendmail=20AUG1999 _ERROR_=0 _N_=4

While it does not give comman delimited format but named format instead

My desired output would be

N,24NOV1999,10OCT1999
S,28DEC1999,13NOV1999
E,03DEC1999,19OCT1999
W,04OCT1999,20AUG1999

Upvotes: 2

Views: 1486

Answers (6)

Tom
Tom

Reputation: 51566

It is easiest to just use a variable list followed by a format list. Syntax is:

(<variable list>) (<format list>)

The values in the format list are repeated until the variables in the variable list are exhausted. The format list can include format modifiers like :,&,~ or = and cursor movement commands like /, +n, or @n.

Also you should add the DSD option to your FILE statement so that missing values are properly represented in the CSV file as having nothing between the delimiters.

So your program reduces to:

data _null_;
  set meeting;
  file 'C:\Users\Desktop\meeting2.txt' DSD dlm=',';
  put (_all_) (:) ;
run;

The problem you had with PUT _ALL_; is that when _ALL_ is used by itself it is treated differently than when it is part of a variable list inside of (). As a variable list it does not include system generated variables such as _N_ or FIRST. or LAST. variables generated by BY statements.

Note that if you want to use _ALL_ in a variable list and still get named output you can use the = format modifier in the format list. 

put (_all_) (=) ;

Upvotes: 1

Joe
Joe

Reputation: 63424

The reason you have so many answers that seem to work, but have different characters, is that the important thing is changing _all_ to (_all_). The arguments after that are not important.

Explained in some detail here, you actually have two entirely different things going on when you write

put _all_;

and

put (_all_) (:);

Programmers familiar with the concept of an overloaded function will find that as the simplest way to think of this. If put sees _all_, it calls one version of put. If it sees (_all_) (or any list of variables with ( ) around it), it calls another (expanding _all_ to its variable list). Notice that if you try

put (_all_);

It fails, and it fails with errors suggesting it is trying to call formatted input (ie, it asks you why you don't have another ( there, which would be the normal thing in formatted input after a list with ( ).)

By itself, _all_ is an argument to put that specifically tells it to use named output to output all variables in the dataset. Hence the variable=value format of the output. So in the first example, _all_ is a constant - an argument - nothing more.

In the second example, though, (_all_) is a variable list, which contains all variables as if they were typed in, space delimited. So

put (_all_) (:);

is equivalent to

put (name sex age height weight) (:);

if used with SASHELP.CLASS. Adding anything - a colon, a tilde, an ampersand, etc. - that is legal in the context of formatted output will cause that to be used.

Note that 

put _all_ @;

Does not cause that to happen - apparently @ (or @@ or / or //) are all legal arguments to put _all_.

Interestingly, _numeric_ and _character_ do not have an analogous shortcut - clearly this is an explicit, special case just for _all_. They cannot be used without parens. put _numeric_; gives an error that _numeric_ is not a legal variable name. But, put (_numeric_) (:); is perfectly legal.

Upvotes: 3

Reeza
Reeza

Reputation: 21264

Try the colon modifier option. 

data _null_;
set meeting;
file 'C:\Users\Desktop\meeting2.txt' dlm=',';
put (_all_) (:);
run;

Another option is to read the names from the SASHELP.VCOLUMN table, create a macro variable that lists the columns and include that in your put statement.

The documentation is a bit scarce: https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000176623.htm

: enables you to specify a format that the PUT statement uses to write the variable value. All leading and trailing blanks are deleted, and each value is followed by a single blank.

~ enables you to specify a format that the PUT statement uses to write the variable value. SAS displays the formatted value in quotation marks even if the formatted value does not contain the delimiter. SAS deletes all leading and trailing blanks, and each value is followed by a single blank. Missing values for character variables are written as a blank (" ") and, by default, missing values for numeric variables are written as a period (".").

Upvotes: 1

user667489
user667489

Reputation: 9569

No, I'm Spartacus!

data _null_;
  set meeting;
  file 'C:\Users\Desktop\meeting2.txt' dlm=',';
  put (_all_) (&);
run;

Upvotes: 0

Ramu
Ramu

Reputation: 165

 This is right one
    data _null_;
    set meeting;
    file 'C:\Users\Desktop\meeting2.txt' dlm=',';
    put (_all_) (~);
    run;

this one helps u

Upvotes: 4

Ramu
Ramu

Reputation: 165

data meeting;

input region $ mtg $ sendmail $;

cards;

N 24NOV1999 10OCT1999

S 28DEC1999 13NOV1999

E 03DEC1999 19OCT1999

W 04OCT1999 20AUG1999

;
   run;


 proc export data=meeting
   outfile='c:\input\meeting.txt'
   dbms=tab replace;

   delimiter=',';

run;

hope this is helpul for even number of of variables.

Upvotes: -1

Related Questions