topic Re: creating a csv file with a ling number field in SAS Procedures

creating a csv file with a ling number field

Sivan — Sun, 28 Apr 2013 11:50:46 GMT

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.

Re: creating a csv file with a ling number field

Ksharp — Sun, 28 Apr 2013 11:55:44 GMT

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

Re: creating a csv file with a ling number field

Sivan — Sun, 28 Apr 2013 13:27:08 GMT

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...

Re: creating a csv file with a ling number field

data_null__ — Sun, 28 Apr 2013 13:46:32 GMT

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;

Re: creating a csv file with a ling number field

Sivan — Sun, 28 Apr 2013 14:02:14 GMT

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;

Re: creating a csv file with a ling number field

data_null__ — Sun, 28 Apr 2013 14:31:24 GMT

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
      

Re: creating a csv file with a ling number field

Sivan — Sun, 28 Apr 2013 15:19:38 GMT

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.

Re: creating a csv file with a ling number field

Peter_C — Sun, 28 Apr 2013 15:22:42 GMT

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

Re: creating a csv file with a ling number field

Sivan — Mon, 29 Apr 2013 06:30:55 GMT

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

Re: creating a csv file with a ling number field

Peter_C — Mon, 29 Apr 2013 07:06:37 GMT

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