BookmarkSubscribeRSS Feed
Sivan
Calcite | Level 5

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.

9 REPLIES 9
Ksharp
Super User

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

Sivan
Calcite | Level 5

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__
Jade | Level 19

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
Calcite | Level 5

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__
Jade | Level 19

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1823 views
  • 0 likes
  • 4 in conversation