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 |
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
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
Beautiful! Curious: where did 254 come from?
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?
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;
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)
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.
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;
I like the put (_all_) statement but it didn't enclose the vars in " ".
If you want all values quoted use the ~ in place of :
put (_all_)(~);
ods _ALL_ CLOSE; ods csv file='/folders/myfolders/want.csv' options(doc='help'); proc report data=sashelp.class nowd noheader; run; ods csv close;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.