topic Re: Output to delimited format in SAS Programming

Output to delimited format

GreggB — Fri, 19 Aug 2016 16:00:42 GMT

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

Re: Output to delimited format

BrunoMueller — Fri, 19 Aug 2016 16:21:59 GMT

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

Re: Output to delimited format

data_null__ — Fri, 19 Aug 2016 16:31:53 GMT

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;

Re: Output to delimited format

GreggB — Fri, 19 Aug 2016 16:32:14 GMT

Beautiful! Curious: where did 254 come from?

Re: Output to delimited format

BrunoMueller — Fri, 19 Aug 2016 16:35:46 GMT

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

Bruno

Re: Output to delimited format

GreggB — Fri, 19 Aug 2016 16:36:57 GMT

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

Re: Output to delimited format

data_null__ — Fri, 19 Aug 2016 17:22:57 GMT

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

put (_all_)(~);

Re: Output to delimited format

GreggB — Fri, 19 Aug 2016 19:26:57 GMT

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?

Re: Output to delimited format

data_null__ — Fri, 19 Aug 2016 19:40:53 GMT

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;

Re: Output to delimited format

ballardw — Fri, 19 Aug 2016 19:40:26 GMT

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)

Re: Output to delimited format

Peter_C — Fri, 19 Aug 2016 19:49:20 GMT

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.

Re: Output to delimited format

Ksharp — Sat, 20 Aug 2016 04:19:44 GMT


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