DATA Step, Macro, Functions and more

Output to delimited format

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

Output to delimited format

 

I have a SAS file that I cleaned up and now need to export it in the same format as the original dirty file. See the portion below. I starred out last and first name for privacy reasons. The original file does not have headers so I can't export those.

 

Original file to mimic:

 

"37-01-017",10045,"Ravenel Elementary","********","********","F",

 

SAS output I currently have:

 

VAR1 student_number VAR3 first_name last_name VAR6
37-01-017 10045 Ravenel Elementary ****** ****** F
37-01-027 10103 FairOak Elementary ****** ****** M
37-01-027 10327 FairOak Elementary ****** ****** F
37-01-022 10332 Walhalla Elementary ****** ****** M
37-01-028 10511 Orchard Park Elementary ****** ****** M

Accepted Solutions
Solution
‎08-19-2016 12:31 PM
SAS Super FREQ
Posts: 682

Re: Output to delimited format

Hi

 

I would use the DATA Step to do this. Maybe there are some macros out there, that do exactly this.

Currently the text is written to the SAS Log, so change the "log" to the "c:\yourname.csv" name you need.

 

The DLM= option will add the commas between the values.

The FORMAT statement assign the $QUOTE. format to all character variable, this cause all character variables to written out with quotes.

 

data _null_;
  set have;
  file log  dlm=",";
  format _character_ $quote254.;
  put
    VAR1 
    student_number 
    VAR3 
    first_name 
    last_name 
    VAR6 
  ;
run;

Bruno

View solution in original post


All Replies
Solution
‎08-19-2016 12:31 PM
SAS Super FREQ
Posts: 682

Re: Output to delimited format

Hi

 

I would use the DATA Step to do this. Maybe there are some macros out there, that do exactly this.

Currently the text is written to the SAS Log, so change the "log" to the "c:\yourname.csv" name you need.

 

The DLM= option will add the commas between the values.

The FORMAT statement assign the $QUOTE. format to all character variable, this cause all character variables to written out with quotes.

 

data _null_;
  set have;
  file log  dlm=",";
  format _character_ $quote254.;
  put
    VAR1 
    student_number 
    VAR3 
    first_name 
    last_name 
    VAR6 
  ;
run;

Bruno

Super Contributor
Posts: 266

Re: Output to delimited format

Beautiful!  Curious: where did 254 come from?

SAS Super FREQ
Posts: 682

Re: Output to delimited format

This will be the max length for any char variable written to the file. You might need to adjust it.



Bruno


Super Contributor
Posts: 266

Re: Output to delimited format

One more question: how can I amend the code to keep the var names and to change the delimiter to a space?  Maybe '09'x?

Respected Advisor
Posts: 3,777

Re: Output to delimited format

[ Edited ]

By keep the var names I assume you mean create a name row.

 

data _null_;
   set sashelp.shoes(obs=25);
   file log ls=256 dsd dlm='09'x;
   if _n_ eq 1 then link names;
   put (_all_)(~);
   return;
names:
   length _name_ $32;
   call missing(_name_);
   do while(1);
      call vnext(_name_);
      if _name_ eq: '_name_' then leave;
      put (_name_)(~) @;
      end;
   put; 
   run; 

 

Super User
Posts: 10,466

Re: Output to delimited format

The space delimiter is easy:

dlm=' '

unless you actually meant tab.

 

A manually build header row would look like:

for comma delimited (placed before the other put statement)

if _n_ then put "var1, student_number,var3,first_name,Last_name,var6";

or replace the comma with spaces for the space delimited. (or the '09'x for tab)

 

 

Valued Guide
Posts: 2,174

Re: Output to delimited format

The FILE statement option DLM= '09'X will deliver tab delimiters.

The FILE statement option DSD will protect all values that might (containing delimiter or " symbols) cause trouble. So the directive to format all character variables with quotes is unneccessary unless there is some other need to have all those quote marks. The DSD option will ensure a numeric value formatted as comma.  will be quoted when the DLM=  produces comma delimiters.

 

Respected Advisor
Posts: 3,777

Re: Output to delimited format

As mentioned you can do that easily with a data step.  I would use the DSD option which will quote values that contain the delimiter.  You use the SAS variable list _ALL_ in the PUT statement so you don't need to mention all the variables.

 

data _null_;
   set sashelp.shoes(obs=50);
   file log dsd;
   put (_all_)(:);
   run;
Super Contributor
Posts: 266

Re: Output to delimited format

I like the put (_all_) statement but it didn't enclose the vars in " ". 

Respected Advisor
Posts: 3,777

Re: Output to delimited format

If you want all values quoted use the ~ in place of :

 

put (_all_)(~);
Super User
Posts: 9,671

Re: Output to delimited format


ods _ALL_ CLOSE;
ods csv file='/folders/myfolders/want.csv' options(doc='help');
proc report data=sashelp.class nowd noheader;
run;
ods csv close;

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 434 views
  • 4 likes
  • 6 in conversation