topic Re: Export SAS file to text or CSV in SAS Programming

Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 14:44:18 GMT

I have a large SAS dataset with 201,000 observations and 2800 variables. I need to export it to multiple CSV or text files that have no more than 1000 columns each. The dataset contains two identifiers studyid and partnum that need to be included in each dataset to remerge them later. I have been trying to use proc export with different delimiters but keep getting a couple hundred extra columns. Is there a way to stip a potential delimiter from all the data to make sure I do not get these type of errors? Also, is there a way to export certain groups of variables, maybe alphabetically or something. Any suggestions would be greatly apprecaited.

Re: Export SAS file to text or CSV

ballardw — Tue, 06 Jun 2017 14:55:16 GMT

You have several options all somewhat ugly depending on your data set names.

One would be to use dataset options to list the variables used

Proc export data=mydataset(keep= idvar1 idvar2 othvar -- anothervar)

outfile ="outdata1.csv"

dbms=csv

replace;

run;

Proc export data=mydataset(keep= idvar1 idvar2 anothervarplus1 -- someothervar)

outfile ="outdata2.csv"

dbms=csv

replace;

run;

Proc export data=mydataset(keep= idvar1 idvar2 someothervarplus1 -- lastvar)

outfile ="outdata3.csv"

dbms=csv

replace;

run;

The -- says to keep all of the variables in order so if you have variables:

var thatvar anothervar somevar yetanothervar

and use Keep = var -- somevar then the result is to keep the first 4 of the above variables.

Re: Export SAS file to text or CSV

Shmuel — Tue, 06 Jun 2017 15:28:10 GMT

The easiest way shoul be:

%macro split_export(keys=, vars=, outnum=);
      proc export data=have(keep=&keys &vars);
          outfile "..path..name&outnum..csv"   /* adapt path and file name prefix */
     run;
%mend split_export;
%split_export(keys=studid partnum, vars=var1-var998 , 1);
%split_export(keys=studid partnum, vars=var999-var1996 ,2);

Of course, if your variable names have no constant prefix (like var...) you shall need more comlicated code,

but it is possible.

Re: Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 15:09:22 GMT

Thank you both for the proposed solutions. I will try both now. My variable names do not have a constant prefix so I will try to modify to accomodate this.

For my delimiter issue, is there something I should do that would help with this or is it a non-issue?

Re: Export SAS file to text or CSV

Shmuel — Tue, 06 Jun 2017 15:19:19 GMT

If you choose dbms=csv the the deimiter is a comma,

otherwise try define dlm= your own delimiter.

If there is no text field that may contain commas then prefer the dbms=csv;

Re: Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 15:18:57 GMT

Thank you. Part of my problem is that there are text fields that contain commas and many other characters so it is difficult to know what delimiter might work best. Is there a stip a certain character from the entire dataset to make sure I can export correctly?

Thank you again for your help!

Re: Export SAS file to text or CSV

Shmuel — Tue, 06 Jun 2017 15:21:50 GMT

Usually text variables don't include tabs.

try dbms=tab or dlm='09'x;

Re: Export SAS file to text or CSV

Tom — Tue, 06 Jun 2017 15:28:10 GMT

1) CSV (or any delimited file) is easy to write with a data step. No need to get complex and try to use proc's to generate them.

2) SAS will put quotes around values that contain delimiters so there is no need to eliminate them from your data. You should check the tool that you are using to read the files and make sure that it understands this basic rule of delimited files.

3) You can use PROC CONTENTS or other methods to get a list of the variables in your file and then break them into sets of less than 1,000 variables. Then use those sets to generate the data steps to write your CSV files.

proc contents data=have out=contents(keep=varnum name) noprint ; run;
proc sort data=contents;
  where lowcase(name) not in ('studyid','partnum');
  by varnum;
run;
data files ;
  group+1;
  do col=3 to 1000 until (eof);
    set contents end=eof;
    output;
  end;
run;
filename code temp;
data _null_;
 file code lrecl=80 ;
 do until (last.group);
   set files ;
   by group ;
   fname = cats('outfile',group,'.csv');
   if first.group then put
    / 'data _null_;'
    / '  set have; '
    / '  file ' fname :$quote. 'dsd lrecl=100000;'
    / '  if _n_=1 then do;'
    / '    put "studyid" @;'
    / '    put "partnum" @;'
   ;
   put '    put ' name :$quote. '@;' ;
   if last.group then put
      '    put;'
    / '  end;'
   ;
 end;
 do until (last.group);
   set files ;
   by group ;
   if first.group then put
    / '  put ( studyid partnum ' @
   ;
   put name @ ;
   if last.group then put
      ') (+0);'
    / 'run;'
   ;
 end;
run;

then just use %INCLUDE to run the generated code.

%include code / source2 ;

Re: Export SAS file to text or CSV

Tom — Tue, 06 Jun 2017 15:38:49 GMT

Why do you need to eliminate the delimiters from the data? SAS will automatically include quotes around values that contain delimiters. What software are you using to read the files that does not understand that?

Re: Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 15:47:44 GMT

I don't need to eliminate delimiters, I was just running into an issue of extra rows being created in the past so was trying to come up with a solution to fix this. I am trying a couple different options now that you all have suggested. Thank you for your promopt responses and help!

Re: Export SAS file to text or CSV

ballardw — Tue, 06 Jun 2017 16:04:23 GMT

@rfarmenta wrote:

I don't need to eliminate delimiters, I was just running into an issue of extra rows being created in the past so was trying to come up with a solution to fix this. I am trying a couple different options now that you all have suggested. Thank you for your promopt responses and help!

"Extra rows" likely because of carriage return or line feed characters in a data field. Proc export will only create one row of output per observation but if the data contains something that another application considers and end of row the result will look like that. If you have one or more data fields that are some sort of unstructed comment or note field you may consider investigating if the variable has such characters as part of the content and remove them prior to export.

Re: Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 17:33:38 GMT

Thank you! I tried that code that Tom sent and when I open the data in excel all of the variable names are in the first cell so when I read that data set into SAS is names all the variables var1-var1000. I am not sure what is going on with that. Also, is there a way to have sepcial missing values just be exported as missing since most other programs do not recognize special missing?

Re: Export SAS file to text or CSV

Shmuel — Tue, 06 Jun 2017 17:49:57 GMT

@rfarmenta wrote: "when I open the data in excel all of the variable names are in the first cell so when I read that data set into SAS is names all the variables var1-var1000. I am not sure what is going on with that."

It is not clear what have you run and what results you got. Please post log of your run and screenshot of the excel.

As to missing values, you can try add next statement before the proc export or the data steps:

options missing=' ';

Re: Export SAS file to text or CSV

Tom — Tue, 06 Jun 2017 18:15:41 GMT

Here is corrected code that will generate the delimiters for the header row manually in the code. The code I tried before with the trailing @ would only work when putting a variable and not a string literal. You could adapt the code to use teh variable list from the list used to generate the code, but then the generated code could not be used later as a stand alone program.

filename code temp;
data _null_;
 file code lrecl=80 ;
 do until (last.group);
   set files ;
   by group ;
   fname = cats('outfile',group,'.csv');
   if first.group then put
    / 'data _null_;'
    / '  set have; '
    / '  file ' fname :$quote. 'dsd lrecl=100000;'
    / '  if _n_=1 then put "studyid,partnum," ' @
   ;
   put  name :$quote. @ ;
   if last.group then put / '  ;' ;
   else put '"," ' @ ;
 end;
 do until (last.group);
   set files ;
   by group ;
   if first.group then put
      '  put ( studyid partno ' @
   ;
   put name @ ;
   if last.group then put
      ') (+0);'
    / 'run;'
   ;
 end;
run;

If you want special missing value to print as missing instead of the letter attached to them then you might need to add more code into the generated program to set the special missing to normal missing.

Something like this would work even if the source data did not have any numeric variables.

 array __N _n_ _numeric_;
 do _n_=1 to dim(__N); if missing(__N(_n_)) then __N(_n_)=.; end;

Similarly if you want to trap CR and/or LF in the character variables you could add code like this to convert them into spaces. The first two lines purposely adds the variable _C_ to insure that the _CHARACTER_ variable list is not empty. You might not need those two lines if your STUDYID variable is also character.

 retain _c_ ' ' ;
 drop _c_;
 array __C _character_;
 do _n_=1 to dim(__C); __C(_n_) = translate(__C(_n_),'  ','0D0A'x); end;

Re: Export SAS file to text or CSV

rfarmenta — Tue, 06 Jun 2017 19:12:47 GMT

Thank you, Tom! This worked wonderfully.

One other thing, is there a way to get rid of all of the tabs in the data? Apparently the group this is being shared with needs the tabs to be removed for their ingestion of the data.

Re: Export SAS file to text or CSV

Tom — Tue, 06 Jun 2017 21:39:23 GMT

If you use the code that I posted to loop over all characters variables and translate CR or LF to spaces you can easily adjust it to also translate the tabs into spaces. Just add one more character to both the TO and FROM arguments to the TRANSLATE() function. The hex code for a tab is '09'x.

Re: Export SAS file to text or CSV

Tom — Tue, 06 Jun 2017 23:02:59 GMT

Here is a simplied version of what I posted before that will write all of the files in one data step. That makes both the code generation easier and the execution faster.

First get the contents and divide into groups. Note that with this verison the sort by VARNUM is critical since postional variable lists (A -- B) will be used later to write the columns. You can change the upper bound of 1000 in the DO loop to control how many variables are written to each file. The lower bound of three is to allow room for the two id variables at the front.

proc contents data=have out=contents(keep=varnum name) noprint ; run;
proc sort data=contents;
  where lowcase(name) not in ('studyid','partnum');
  by varnum;
run;
data files ;
  group+1;
  do col=3 to 1000 ;
    set contents;
    output;
  end;
run;

Now use this file to both write the header lines to each of the output files and also write the code needed to write the data lines. Modify the statement that creates the FNAME variable to match your file naming requirement. Note the inclusion of the MOD option in the generated FILE statements. That is because the header line will already be written be this step and the step that writes the data just needs to append to the existing files. Also note it is using a line length of 1,000,000 which should be more than enough for 1,000 variables as long as they aren't all extremely long character variables. SAS can support longer line lengths, but it might depend on your operating system.

filename code temp;
data _null_;
   set files ;
   by group ;
   fname = cats('outfile',group,'.csv');
   file csv filevar=fname dsd lrecl=1000000 ;
   if first.group then put 'studyid,partnum,' @;
   put name @ ;
   if last.group then put ;
   file code ;
   if first.group then put
    / 'file ' fname :$quote. 'dsd mod lrecl=1000000;'
    / 'put ( studyid partnum ' name '-- ' @
   ;
   if last.group then put name ') (+0);' ;
run

Now write the data lines. Notice that the included code only had the FILE and PUT statements so the rest of the data step is just plain code that should be easier to edit. I included loops to handle both the special missing value issue and blanking out tabs, cr and lf characters. I used two SET statements to force STUDYID and PARTNUM to the beginning of the data vector so that the postional variable lists will not include them. I used the INDSNAME= option on the second SET just to make sure there was at least one character variable defined for the _character_ variable list used in the array definition. I also used the DO OVER style loops that SAS is trying to remove from the documentation. It is just so much easier to not have to code indexes for operations that you want to apply to all members of an array.

data _null_;
  set have (keep=studyid partnum);
  set have (drop=studyid partnum) indsname=__C__;
  array __C _character_;
  array __N _n_ _numeric_;
  do over __C ; __C=translate(__C,'   ','090A0D'x); end;
  do over __N ; if missing(__N) then __N=.; end;
  %include code / source2 ;
run;

Re: Export SAS file to text or CSV

rfarmenta — Wed, 07 Jun 2017 17:11:01 GMT

Thank you, Tom! This new code is definitely more efficent. This is exactly what I needed and I appreciate your help.

One last question, I am being asked to remove commas that may exist in character values as well. Can I just use this modified version of what you wrote:

do over __C ; __C=translate(__C,' ','002C'x); end;

I am not sure that is the right HEX code for commas though.

Thank you again!

Re: Export SAS file to text or CSV

Tom — Wed, 07 Jun 2017 17:15:07 GMT

'2C'x is the right code for a comma. But you could also use '00'x||',' as to specify the two character string of a binary zero and a comma.

Thank you, Tom! This new code is definitely more efficent. This is exactly what I needed and I appreciate your help.

One last question, I am being asked to remove commas that may exist in character values as well. Can I just use this modified version of what you wrote:

do over __C ; __C=translate(__C,' ','002C'x); end;

I am not sure that is the right HEX code for commas though.

Re: Export SAS file to text or CSV

rfarmenta — Wed, 07 Jun 2017 17:50:43 GMT

I just did some additional checks of my data and there apprears to be an issue with the CSV files that are being created. Some of the variable names are not being matched up correctly with the data. For example, the data for BMI and weight are reversed so the BMI variable has the weight values and the weight variable has the BMI values.

Do you know what might be going on to make this happen?

Thank you!