CODEWITHSUNDEEP
excelsas
Kazu
Kazu

Reputation: 137

Error when exporting from SAS to Excel - comma problem

so I have a code like this, when this executes

 data t4;
    put new_EMPLID $2000.;
    do until(last.CRSE_ID);
    set t3;
    by CRSE_ID notsorted;
      new_EMPLID = catx(',',new_EMPLID,compress(EMPLID));
    END;
    drop EMPLID;
    run;

output on SAS is

enter image description here

THEN "View in Excel"

enter image description here

I want to show LIKE THIS "2234944,2330002" exactly like output on SAS

Why excel is changing like pic? Does anybody know how to fix this issue???

I know , is cauing issue, when I changed to 'x', it worked on excel.

Upvotes: 0

Views: 538

Answers (1)

Richard
Richard

Reputation: 27508

What operating system, SAS version and Excel version and bittiness of each ?

What code did you submit ?

This sample code with Proc EXPORT exhibits no issues in Windows 10, SAS 9.4M4 and Excel 2016 64-bit.

If a courseId has only one attendee, or the if commas could be misinterpreted by Excel they will be :). Your language settings can also have influence the Excel rendering.

data have; input
courseId employeeId; datalines;
1 2234944
1 2330002
1 1975365
1 2244221
2 1122334
2 2233445
2 3344551
run;

data want;
  do until (last.courseId);
    set have;
    by courseId;
    length attendeeList $2000;
    attendeeList = catx(',',attendeeList,employeeId);
  end;
  drop employeeId;
run;

proc export data=want dbms=excel replace file='c:\temp\course_attendence.xlsx';
run;

Upvotes: 1

Related Questions