Learning SAS? Welcome to the exclusive online community for all SAS learners.

Extracted data into excel looks messy

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Extracted data into excel looks messy

Hi,

How should I extract SAS data to Excel ?  I extracted data into excel but it looks bad. I am not able to paste the screen shot here. The data looks unorganised and messy.I tried using nocol and norow (with or without) but It looks the same.

Thanks for your help,

Nisha


Accepted Solutions
Solution
‎11-19-2014 08:15 AM
Super User
Super User
Posts: 7,962

Re: Extracted data into excel looks messy

Well, I wouldn't use HTML ods destination now ExcelXP tagset is available.  For my preference I prefer proc report output, so whilst others may be able to suggest for freq output I would do (note only pseudocode, I don't know off the top of my head what dataset freq creates, so change variable names and things):

proc freq;

     output out=work.freq_results;

     tables senior*(typmen5 zeat tuu typlog stalog)/nopercent nofreq norow chisq;

     format typmen5 $ftypmen. zeat $fzeat. tuu ftuu. typlog $ftyplog. stalog $fstalog.;

     weight pondmen;

run;

ods tagsets.excelxp file="/folders/myfolders/TD.xlsx";

title1 "...";

footnote1 '...';

proc report data=work.freq_results nowd split='|';

     columns /* Insert the columsn to be output */;

     define     col1  /* update per above */  / "Total" style(column)=[just=c cellwidth=2cm] style(header)=[just=c];

     define ...

run;

ods tagsets.excelxp close;

View solution in original post


All Replies
Contributor
Posts: 65

Re: Extracted data into excel looks messy

It might be because of column width, of the excel sheet.

Its hard to say how to fix it without knowing the data that is been exported.

If you can provide more specifics on the data that's been exported that would be helpful

Occasional Contributor
Posts: 7

Re: Extracted data into excel looks messy

Posted in reply to DMoovendhan

Thanks for your help. Here is the screen shot:

TD.png

Super User
Super User
Posts: 7,962

Re: Extracted data into excel looks messy

How are you getting the data into Excel?  Are you using ods tagsets.excelxp with a proc report statement?  If not I would recommend looking at those two.  You can get quite a lot of control over the output with that setup.  If you want anything further please specify further.

Occasional Contributor
Posts: 7

Re: Extracted data into excel looks messy

ods html file='/folders/myfolders/TD.xls';

proc freq;

tables senior*(typmen5 zeat tuu typlog stalog)/nopercent nofreq norow chisq;

format typmen5 $ftypmen. zeat $fzeat. tuu ftuu. typlog $ftyplog. stalog $fstalog.;

weight pondmen;

run;

ODS html CLOSE;

do you think it's correct?

Solution
‎11-19-2014 08:15 AM
Super User
Super User
Posts: 7,962

Re: Extracted data into excel looks messy

Well, I wouldn't use HTML ods destination now ExcelXP tagset is available.  For my preference I prefer proc report output, so whilst others may be able to suggest for freq output I would do (note only pseudocode, I don't know off the top of my head what dataset freq creates, so change variable names and things):

proc freq;

     output out=work.freq_results;

     tables senior*(typmen5 zeat tuu typlog stalog)/nopercent nofreq norow chisq;

     format typmen5 $ftypmen. zeat $fzeat. tuu ftuu. typlog $ftyplog. stalog $fstalog.;

     weight pondmen;

run;

ods tagsets.excelxp file="/folders/myfolders/TD.xlsx";

title1 "...";

footnote1 '...';

proc report data=work.freq_results nowd split='|';

     columns /* Insert the columsn to be output */;

     define     col1  /* update per above */  / "Total" style(column)=[just=c cellwidth=2cm] style(header)=[just=c];

     define ...

run;

ods tagsets.excelxp close;

Occasional Contributor
Posts: 7

Re: Extracted data into excel looks messy

thank you very much.

I have a question regarding the format. what should I put as code so that I can get the percentage in total not the number as in 3415339 etc. I want to get the date in percentage . I need to present it in the form of graphics.

proc freq;

table profession*agepr/norow nofreq nopercent;

format agepr fpre.;

weight pondmen;

run;

tdd.png

Super User
Super User
Posts: 7,962

Re: Extracted data into excel looks messy

Well, there is a syntax for invoking Excel formats:

define a / style(column)=[htmlstyle="mso-number-formatSmiley Tongueercent"];

http://www2.sas.com/proceedings/sugi28/012-28.pdf

However I find working with these a bit hit and miss.  I would put a datastep between the freq and report, apply any formatting there, and then output that.  I also tend to put all data out as text rather than numeric, generally because Excel starts putting number stored as text or formatting at its end which I don't like.  I prefer to create the output as I want to see it as a dataset, then output that so the Excel output matches as near as possible.  Otherwise Excel tries to be "helpful".

Occasional Contributor
Posts: 7

Re: Extracted data into excel looks messy

Well, I thought I could get the result while adding one of the norow or any other options but I think the result I want needs a specific syntax. Thanks for the link anyway. The information provided is too large.

Super User
Posts: 11,343

Re: Extracted data into excel looks messy

Different proc may work:

proc tabulate;

class profession;

class agepre;

table profession all='Total',

          agepr*colPctN=''*f=5.2;

format agepr fpre.;

weight pondmen;

run;

Though the total % should be 100

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 648 views
  • 3 likes
  • 4 in conversation