CSV formatting issue

Reply
Occasional Contributor
Posts: 5

CSV formatting issue

Hi All,

I have a weird formatting issue when exporting to CSV file and I can figure out how to get around it. I tried to set informat/format option but that didn't work either. So basically sas values like 1-2, 10/40 prints out in partial date format as jan-2 and oct-40 respectively! What do i do to print the actual values?

For example:

data test;

  INFORMAT val $40.;

  val='0.04/10';

   output;

  val='1/2';

  output;

   val='10/40';

  output;

  val='5.2';

  output;

  val='300/500';

  output;

  format val $40.;

run;

  ods csv file="/path./TESTK.csv";

    proc print data= test label noobs;

        run;

    ods csv close;

The following outputs on csv:

val

0.04/10

2-Jan

Oct-40

5.2

300/500

Super User
Posts: 19,870

Re: CSV formatting issue

I'm guessing you're opening your CSV via Excel?

The issue is with Excel converting these fields. If you open the file with a text editor does it show correctly?

Occasional Contributor
Posts: 5

Re: CSV formatting issue

Yes with the text file they open fine.

Is there a way to force them to read as text field?

Super User
Posts: 7,863

Re: CSV formatting issue

Ask Microsoft.

SCNR

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,997

Re: CSV formatting issue

I agree with Jagadishkatam here.  You need to identify what you are trying to achieve, is is a report - I.e. something which will be read or presented, or are you doing a data transfer.  The two are different.  The first has formatting, layout, and such like which makes nice presentation and easy reading.  This would be word, pdf, Excel outputs which are not used further down the line for processing - i.e. this is a Report.  If this is what you intend then read up on proc report and the ods tagsets as you have full functionality with the output.

If you are creating a data transfer format, then the above does not apply.  What you want from that is conformity to a tabular data structure with delimiters, and brief information.  This consists of the information needed to re-create a dataset from the supplied information.  It is not setup to be viewed easily (although if you went xml and css then you could have raw data and fonts, formats etc).  It is purely for moving data from one system or process to another.  There are many other different formats for different purposes, e.g. XML, XPT, Json.  You will note that Excel is not on that list as Excel is not for <insert your task here>.

Super User
Super User
Posts: 7,077

Re: CSV formatting issue

The file is fine, you are just opening it with the wrong application.

You can see for yourself by adding another step to your program.

data _null_;

  infile "/path./TESTK.csv";

  input;

  put _infile_;

run;

Trusted Advisor
Posts: 1,137

Re: CSV formatting issue

Thought to share a similar thread. Hope it helps

Thanks,

Jag

Thanks,
Jag
Ask a Question
Discussion stats
  • 6 replies
  • 352 views
  • 0 likes
  • 6 in conversation