BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

11 REPLIES 11
BrunoMueller
SAS Super FREQ

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

GreggB
Pyrite | Level 9

Beautiful!  Curious: where did 254 come from?

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



Bruno


GreggB
Pyrite | Level 9

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?

data_null__
Jade | Level 19

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; 

 

ballardw
Super User

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)

 

 

Peter_C
Rhodochrosite | Level 12

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.

 

data_null__
Jade | Level 19

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;
GreggB
Pyrite | Level 9

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

data_null__
Jade | Level 19

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

 

put (_all_)(~);
Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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