topic Re: csv file with negative sign behind value in SAS Procedures

csv file with negative sign behind value

Dave — Fri, 01 Jul 2011 11:14:53 GMT

I imported a csv file with a negative sign behind the number. SAS seems to have dropped the negative signs on numeric values that had it.

original format 1.79-;114-;116

output has 1.79 and 114 and 116

How do I import and keep the negative sign where it exists.

Thanks

Re: csv file with negative sign behind value

data_null__ — Fri, 01 Jul 2011 11:24:49 GMT

I don't know if PROC IMPORT will handle this properly. You can roll your own or modify the code that PROC IMPORT produces.

The informat you need is TRAILSGN

353 data _null_;

354 infile cards dsd dlm=';';

355 input (a1-a3) (:TRAILSGN.);

356 put (_all_)(=);

357 cards4;

a1=-1.79 a2=-114 a3=116

Re: csv file with negative sign behind value

Dave — Fri, 01 Jul 2011 12:10:12 GMT

Thanks. This is my first attempt at SAS so apologies for silly questions. I took your code and put it in the log file under INFORMAT. I changed (A1-A3) to match the names under informat that correspond to value cells. After I pasted it in, I clicked "Run" and it says Details and 1 of 2 running but it may be hanging? FYI, the code file does show a line across after Informat before the code I pasted.

Am I doing something wrong?

example:

data _null_;

infile cards dsd dlm=';';

input (CAT) (:TRAILSGN.);

input (GAM) (:TRAILSGN.);

input (SAL) (:TRAILSGN.);

put (_all_)(=);

cards4;

csv file with negative sign behind value

data_null__ — Fri, 01 Jul 2011 12:18:10 GMT

Like this....

data _null_;

infile cards dsd dlm=';';

input (cat gam sal) (:TRAILSGN.);

put (_all_)(=);

cards4;

1.79-;114-;116

;;;;

run;

You will need to modify the code that reads YOUR data with the correct INFORMAT.

Re: csv file with negative sign behind value

manojinpec — Fri, 01 Jul 2011 12:26:01 GMT

Can you provide the log pls?

Re: csv file with negative sign behind value

data_null__ — Fri, 01 Jul 2011 12:47:22 GMT

I think you need to show your code. My code is only an example of the INFORMAT that you need to use. You will need to include it in the code that you write to read the CSV file.

Re: csv file with negative sign behind value

Dave — Sat, 02 Jul 2011 20:12:30 GMT

Yes, sorry for delay. I have not bothered yet to name all the variables for this since the last 3 variables or columns are are the only ones that I am having issues with. Thanks for taking the time to help. Here is a copy of the log:

/* --------------------------------------------------------------------

Code generated by a SAS task

Generated on Saturday, July 02, 2011 at 4:00:32 PM

By task: Import Data

Source file: E:\David's Documents\Clients\Stats\Data\Data query - Run\reports\file1.CSV

Server: Local File System

Output data: WORK.file1

Server: Local

-------------------------------------------------------------------- */

DATA WORK.AA_HT1;

LENGTH

F1 8

F2 8

F3 $ 6

F4 $ 3

F5 $ 1

F6 8

F7 8

F8 $ 3

F9 $ 3

F10 $ 3

CAT 8

GAM 8

SAL 8 ;

FORMAT

F1 BEST10.

F2 BEST18.

F3 $CHAR6.

F4 $CHAR3.

F5 $CHAR1.

F6 YYMMDD10.

F7 DDMMYY10.

F8 $CHAR3.

F9 $CHAR3.

F10 $CHAR3.

CAT BEST9.

GAM BEST10.

SAL BEST10. ;

INFORMAT

F1 BEST10.

F2 BEST18.

F3 $CHAR6.

F4 $CHAR3.

F5 $CHAR1.

F6 YYMMDD10.

F7 DDMMYY10.

F8 $CHAR3.

F9 $CHAR3.

F10 $CHAR3.

CAT BEST9.

GAM BEST10.

SAL BEST10. ;

INFILE 'C:\Users\David\AppData\Local\Temp\SEG1844\file1-bd5b8ded924346ca9b53f0e328e93c43.txt'

LRECL=98

ENCODING="WLATIN1"

TERMSTR=CRLF

DLM='7F'x

MISSOVER

DSD ;

INPUT

F1 : ?? BEST10.

F2 : ?? BEST18.

F3 : $CHAR6.

F4 : $CHAR3.

F5 : $CHAR1.

F6 : ?? YYMMDD8.

F7 : ?? DDMMYY6.

F8 : $CHAR3.

F9 : $CHAR3.

F10 : $CHAR3.

CAT : ?? COMMA9.

GAM : ?? COMMA10.

SAL : ?? COMMA10. ;

RUN;

Re: csv file with negative sign behind value

data_null__ — Sat, 02 Jul 2011 20:44:52 GMT

INPUT

F1 : ?? BEST10.

F2 : ?? BEST18.

F3 : $CHAR6.

F4 : $CHAR3.

F5 : $CHAR1.

F6 : ?? YYMMDD8.

F7 : ?? DDMMYY6.

F8 : $CHAR3.

F9 : $CHAR3.

F10 : $CHAR3.

CAT : ?? TRAILSGN.

GAM : ?? TRAILSGN.

SAL : ?? TRAILSGN. ;

RUN;

Re: csv file with negative sign behind value

Dave — Sat, 02 Jul 2011 20:53:43 GMT

Thanks!

Re: csv file with negative sign behind value

Dave — Sun, 03 Jul 2011 22:15:36 GMT

Hi data_null. I may have some data manipulations and analysis to do. I am considering working with a consultant(s) and am making a few inquiries in case. I have added you as a friend so you can reply there if you wish. Thanks.

Re: csv file with negative sign behind value

Ksharp — Mon, 04 Jul 2011 06:16:30 GMT

Hi. Dave

I have received your information.Can you send me some detail about your problem?

Thanks.

Ksharp