creating a csv file with a ling number field

[Sivan]

Hi,

I'm trying to create a CSV file based on a data set where one of the variables is a 16 digits number, but the number is trimmed in the CSV file.

Here is the code I'm using :

     data _null_ ; 

         set frd_tag.fraud_tagging_&start_date1._&end_date1.;

         FILE  "\\icc-tlv\root\dwh\BOXI\I_SHAARO\fraud_tagging_daily\reports\fraud_tagging_&start_date1._&end_date1..csv" DLM=',' ;

         PUT  org pan transaction_id1 transaction_id2 fraud_type ;

     run ;

The long number variable is "pan".

What can I do to have this variable saved properly?

Thanks,

Sivan.

[Ksharp]

Did you try to use proc export to make this CSV file ?

[Sivan]

I just tried the following code :

proc export data=frd_tag.fraud_tagging_&start_date1._&end_date1.

outfile="\\icc-tlv\root\dwh\BOXI\I_SHAARO\fraud_tagging_daily\reports\fraud_tagging_&start_date1._&end_date1..csv"

dbms=csv

replace

;

run;

I got the same results...

[data_null__]

adding a format seems to do the trick.

data class;
   set sashelp.class;
   long=1234567890123456;
   run;
data _null_;
   file log dsd;
   set class;
   put (_all_)(:);
   format long 16.;
   run;

[Sivan]

It works when I put the results in the log, but not when I'm creating a CSV file,

as the following :

data _null_;

   *file log dsd;

   FILE  "\\icc-tlv\root\dwh\BOXI\I_SHAARO\fraud_tagging_daily\reports\fraud_tagging_&start_date1._&end_date1..csv" DLM=',' ;

   set class;

   put (_all_)(:);

   format long 16.;

   run;

[data_null__]

I'm thinkin' your gonna have to show your result.  You sayin' it don't work aint tellin' me nuttin'.

.

18         options generic=1;
19         data class;
20            set sashelp.class;
21            long=1234567890123456;
22            run;

NOTE: The data set WORK.CLASS has 19 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

23         filename FT47F001 '~/FT47F001.csv';
24         data _null_;
25            file FT47F001 dsd;
26            set class;
27            put (_all_)(:);
28            format long 16.;
29            run;

NOTE: The file FT47F001 is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 19 records were written to the file (system-specific pathname).
      The minimum record length was 34.
      The maximum record length was 38.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

30         data _null_;
31            infile FT47F001;
2                                                          The SAS System                               06:41 Sunday, April 28, 2013

32            input;
33            list;
34            run;

NOTE: The infile FT47F001 is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
1         Alfred,M,14,69,112.5,1234567890123456 37
2         Alice,F,13,56.5,84,1234567890123456 35
3         Barbara,F,13,65.3,98,1234567890123456 37
4         Carol,F,14,62.8,102.5,1234567890123456 38
5         Henry,M,14,63.5,102.5,1234567890123456 38
6         James,M,12,57.3,83,1234567890123456 35
7         Jane,F,12,59.8,84.5,1234567890123456 36
8         Janet,F,15,62.5,112.5,1234567890123456 38
9         Jeffrey,M,13,62.5,84,1234567890123456 37
10        John,M,12,59,99.5,1234567890123456 34
11        Joyce,F,11,51.3,50.5,1234567890123456 37
12        Judy,F,14,64.3,90,1234567890123456 34
13        Louise,F,12,56.3,77,1234567890123456 36
14        Mary,F,15,66.5,112,1234567890123456 35
15        Philip,M,16,72,150,1234567890123456 35
16        Robert,M,12,64.8,128,1234567890123456 37
17        Ronald,M,15,67,133,1234567890123456 35
18        Thomas,M,11,57.5,85,1234567890123456 36
19        William,M,15,66.5,112,1234567890123456 38
NOTE: 19 records were read from the infile (system-specific pathname).
      The minimum record length was 34.
      The maximum record length was 38.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

[Sivan]

The problem happens when I open the CSV file with excel, and the number looks like this :

1.23E+15

I have a technical problem attaching the file here, but I can send it to you by mail if you give me your adress.

Thanks,

Sivan.

[Peter_C]

It matters HOW you view your CSV file.

Excel will assume a 16 digit number is to be stored as a number and show it in General format (and convert the 16th digit to 0)

So check your CSV file in notepad.

peterC

[Sivan]

I have to open it with excel...

Maybe I should have defined my question differently - you can say that I need to export the 16 digit number variable to excel, is it possible?

Thanks,

Sivan

[Peter_C]

csv format holds not enough information to direct that a 16digit field should be teated as text instead of number.

Either change the number to be preceeded with a blank character other than blank (x'ff' for example)

or the more professional route = change to ODS tagsets.excelxp destination where you cha define the style on proc print var statement demanding TEXT format for the cell.

You might find postings on the ods reporting forum that help. Look for credit card numbers

peterC