Exporting data to excel

[tanya24lynn]

Hello Arthur,

First, thank you for posting this code--I found it very helpful as it allowed me to export  (to csv) all of my variables names (first row as header). However, around rows 20 I am having another issue where rows are no long being read correctly. This can be seen when I open up the txt file and ID variable is now blank and other variable values following are incorrect. This may be due to the fact that I have some very long character variables? Other than this idea I am at a loss and have spent 3 days trying to rectify this issue with no luck. Any help would be greatly appreciated.

[Reeza]

Do your text fields have commas in them?

[tanya24lynn]

Yes, they do have commas--sometimes more than one (var1, var2,,,,,,,,,var3 for example). Some of my character variables are greater than 200 in length so I tried formatting them to 199 in a dataset but this still did not work.

[Reeza]

How are you opening the text file to view it? Make sure to check it in a text editor, not Excel, and make sure that wrapping isn't turned on.

[tanya24lynn]

I was viewing in both. I will check the wrapping. Strangely when I dropped the long character variables (comment variables), the export worked. Not a great fix but it's something. Thanks for your help!

[Reeza]

Is the variable in quotes, if not, quote it before you export it.

[tanya24lynn]

Good idea. However, I am not sure how to do this. I used the code Arthur provided as it dealt with the issue of the large number of variables I have in my dataset (code again below). Not sure how I can modify this to specify each variable in the dataset to export (I have about 20 character long comment variables and 1000s of other clinical variables). Thanks again.

/*create a format to use*/

proc format;

  value codes

    999='Yes';

run;

/*create a test data set*/

data whatever;

  retain a1-a1000 (1000*"just some test stuff, i.e., meaningless filler");

  retain b1-b1000 (1000*999);

  format b1-b1000 codes.;

  do _n_=1 to 500;

    output;

  end;

run;

proc sql noprint;

  select '"'||trim(name)||'"'

    into :names

      separated by " ','"

        from dictionary.columns

          where libname eq "WORK" and

                memname eq "WHATEVER"

  ;

quit;

data _null_;

  set whatever;

  file "c:\whatever.csv" dlm=',' dsd lrecl=200000;

  format _all_;

  if _n_ eq 1 then put &names.;

  put (_all_) (+0);

run;

[Tom]

You should open a new thread rather than extending this one.

Do your values include carriage return and line feed characters?

Did you set the LRECL for out text large enough for all of the data?  Check the NOTEs for the step that wrote the data and see if it says it wrote more lines to the text file than the number of observations it read from the dataset.  If so then SAS wrapped the lines because you exceeded the line length.

[art297]

Based on your earlier comment that the output contained formatted values, when you actually need to output the raw data, I'd add a datastep before Tom's suggested code, namely:

data need;

  set whatever;

  format _all_;

run;

PROC EXPORT DATA= need

            OUTFILE= "c:\whatever.csv"

            DBMS=CSV REPLACE;

     PUTNAMES=YES;

RUN;

[sara122]

I tried this code and it seemed to work, except that it still output formatted values. Do you have any suggestions for resolving this?

[art297]

The more I think about your problem, I still think a simple data step would provide the easiest solution.  The three problems you found when I initially suggested it (namely, lrecl not being set, formated values being output, and the output not including variable names) are all easy to fix.

As such, I suggest your trying the following approach:

/*create a format to use*/

proc format;

  value codes

    999='Yes';

run;

/*create a test data set*/

data whatever;

  retain a1-a1000 (1000*"just some test stuff, i.e., meaningless filler");

  retain b1-b1000 (1000*999);

  format b1-b1000 codes.;

  do _n_=1 to 500;

    output;

  end;

run;

proc sql noprint;

  select '"'||trim(name)||'"'

    into :names

      separated by " ','"

        from dictionary.columns

          where libname eq "WORK" and

                memname eq "WHATEVER"

  ;

quit;

data _null_;

  set whatever;

  file "c:\whatever.csv" dlm=',' dsd lrecl=200000;

  format _all_;

  if _n_ eq 1 then put &names.;

  put (_all_) (+0);

run;

[sara122]

What is the purpose of creating a test data set? and what am I supposed to fill in for "just some test stuff, i.e., meaningless filler"? When I ran the code whatever I replaced it with became the value for all of my observations. I really appreciate all of the help!

[art297]

The first part, creating a format to use, you do have to run;

The second part, create some test data, was solely for the purpose of having some test data that I could use.  You, of course, should skip that step.

The third and fourth parts, (i.e., the proc sql and data _null_ step) and what you need, HOWEVER:

1, In the proc sql, the libname and memname should be changed to reflect where your data reside and what the file is called (although they MUST be entered in UPPERCASE).

2. in the second line of the data step, set whatever;, 'whatever' should be changed to the one or two level filename of the data that you want to export

3. in the third line of the data step, file "c:\whatever.csv" dlm=',' dsd lrecl=200000;,

"c:\whatever.csv"should be changed to reflect the name of the file you are trying to create.

[jakarman]

The most easy way to drop tables to Excel would be using a libname. The best is using examples and a paper as the SAS docs are not very clear on this topic.

An already old paper doing this well  is: http://www2.sas.com/proceedings/sugi31/024-31.pdf  The dino-time is even older than this.