BookmarkSubscribeRSS Feed
PaigeMiller
Diamond | Level 26

I have this unusual need to be able to turn a SAS data set into an Excel file (either .xls or .xlsx is fine), but all the ways I have tried produce an Excel file with the first row containing either the SAS variable names or the SAS variable labels. I don't want this first row of the Excel file to contain the SAS variable names or variable labels. I want the first row of the Excel file to contain the first row of data in the SAS data set, and the 2nd row of the Excel file to contain the second row of data in the SAS data set, and so on.

How can I do this?

--
Paige Miller
9 REPLIES 9
Cynthia_sas
SAS Super FREQ

Hi:

  If you use ODS and PROC REPORT with the NOHEADER option, this is possible. You can create a CSV file to start and then switch to other destinations as needed/wanted (primarily if you want style information).

Cynthia

ods csv file='c:\temp\nolabels.csv';

     

proc report data=sashelp.class nowd noheader;

column name age sex height weight;

run;

       
ods csv close;

Tom
Super User Tom
Super User

ods tagsets.excelxp will work with that also and is closer to a "real" excel file than a CSV file is.

PaigeMiller
Diamond | Level 26

Cynthia, a CSV file will not work here, as then I have to, within the program, convert CSV to Excel, and I don't know how to do that

Tom, can you give an example, as I couldn't get this to work using OSD TAGSETS.EXCELXP, and furthermore it creates an .xml file which creates the same issue for me, I don't know how to convert to XLS or XLSX

--
Paige Miller
data_null__
Jade | Level 19

What is your OS?   If you're on windows you could use PROC EXPORT then use VBSCRIPT or other scripting language to open the XLSX, delete the first row and save it.  I've done similar in the past and there are some examples around of similar edits but I no longer have PC SAS so my usefulness is nil.

PaigeMiller
Diamond | Level 26

I am using Windows 7. I have no experience with VBSCRIPT or other scripting language, which is why I would like to do this entirely in SAS if possible.

--
Paige Miller
art297
Opal | Level 21

: Given your other post this morning, regarding dde commands, in addition to the two references I posted in that thread, look at ALL of Koen Vyverman's papers or posts on SAS-L by Kilovolt.

An example that probably provides exactly what you are looking for can be found at:

http://www2.sas.com/proceedings/sugi27/p190-27.pdf

PaigeMiller
Diamond | Level 26

, thank you very much.

 

Now I do remember using these techniques in the paper by Vyverman in the past, and at that time, with different needs, I discovered that ODS TAGSETS.EXCELXP was a better way to go. Furthermore, I also remember that something about the techniques in the paper by Vyverman had broken (I don't recall exactly what broke, or exactly what broke it, might have been Sas 9 or Office 2010) and I feel somewhat uncomfortable using techniques which Vyverman says were "developed, tested, and approved of on a Windows NT4 (SP6) system, running release 8.2 of the SAS System and MS Office97."

That doesn't mean I have ruled out the methods in Vyverman's paper, but when I investigate the entirety of X4ML commands, I do not see anything resembling a DELETE ROWS command, under any name that I can think of.

So that brings us back to ODS TAGSETS.EXCELXP, which creates an XML file, and thanks to , I believe I can now convert these XML files to XLSX, and using Cynthia's NOHEADER option in PROC REPORT, I think that solves the problem. At least, its the step I'm going to try next, but it seems like I can meet all requirements. I will let you know if it works.


--
Paige Miller
art297
Opal | Level 21

: Just in case you run into problems with the tagsets approach, below is a variant of a method that I and some others presented at the last MWSUG meeting (p.s. and will be presenting, again, at this year's SGF; see:

A Poor/Rich SAS Users Proc Export - sasCommunity )

It'a an approach that uses proc fcmp to copy a sas dataset, and then creates and runs a vbs script to paste the data into an Excel workbook and, finally, saves the workbook.  The lines I commented out would have copied the variable names:

%macro copy2excel(lib=,mem=,outpath=,outwb=);

  proc fcmp outlib=work.func.util;

    function c2cb(lib $,mem $);

      rc=filename('clippy',' ','clipbrd');

      if rc ne 0 then return(1);

      fid=fopen('clippy','o',0,'v');

      if fid eq 0 then do;

        rc = filename( 'clippy' );

        return(2);

      end;

      dsid=open(catx('.',lib,mem));

      if dsid eq 0 then do;

        rc=fclose(fid);

        rc=filename('clippy');

        return(3);

      end;

      rc=attrn(dsid,'any');

      if rc ne 1 then do;

        rc=fclose(fid);

        rc=close(dsid);

        rc=filename('clippy');

        return(4);

      end;

      nvar=attrn(dsid,'nvar');

      array v[1] /nosymbols;

      call dynamic_array(v,nvar);

        do i = 1 to nvar;

/*          if i gt 1 then rc=fput(fid,'09'x);*/

/*          rc=fput(fid,varname(dsid,i));*/

          v=ifn( vartype(dsid,i)='C',1,2);

        end;

/*        rc=fwrite(fid);*/

      do i=1 to attrn(dsid,'nlobs');

        rc=fetchobs(dsid,i);

        do j=1 to nvar;

          if j gt 1 then rc=fput(fid,'09'x);

          if (v eq 1) then rc=fput(fid, getvarc(dsid,j));

          else do;

            fmt=varfmt(dsid,j) ;

            if missing(fmt) then fmt='best12.';

            rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.'));

          end;

        end;

        rc=fwrite(fid);

      end;

      rc=fclose(fid);

      rc=close(dsid);

      rc=filename('clippy');

      return(0);

    endsub;

  quit;

  %local cmplib;

  %let cmplib=%sysfunc(getoption(cmplib));

  options cmplib=(work.func);

  %put %sysfunc(c2cb(&lib,&mem)) ;

  options cmplib=(&cmplib);

  data _null_;

    length script filevar $256;

    script = catx('\',pathname('WORK'),'PasteIt.vbs');

    filevar = script;

    file dummy1 filevar=filevar recfm=v lrecl=512;

   

    put 'Dim objExcel';

    put 'Dim Newbook';

   

    put 'Set objExcel = CreateObject("Excel.Application")';

    put 'Set Newbook = objExcel.Workbooks.Add()';

    put 'objExcel.Visible = True';

    put 'Newbook.Sheets("Sheet1").Activate';

    put 'Newbook.Worksheets("Sheet1").Paste';

    put 'Newbook.SaveAs("'@;

    put "&outpath.&outwb."@;

    put '")';

    put "Newbook.Close";

   

    put 'objExcel = Nothing';

    put 'Newbook = Nothing';

    script="'"||'cscript "'||trim(script)||'"'||"'";

    call symput('script',script);

  run;

  data _null_;

    call system(&script.);

  run;

%mend copy2excel;

options NOQUOTELENMAX;

%copy2excel(lib=sashelp,

            mem=class,

            outpath=d:\art\,

            outwb=exceldelete.xlsx)

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
  • 9 replies
  • 1585 views
  • 0 likes
  • 6 in conversation