Help using Base SAS procedures

csv file with negative sign behind value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

csv file with negative sign behind value

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


Accepted Solutions
Solution
‎07-02-2011 04:44 PM
Respected Advisor
Posts: 3,799

Re: csv file with negative sign behind value

    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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: csv file with negative sign behind value

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

Occasional Contributor
Posts: 7

Re: csv file with negative sign behind value

Posted in reply to data_null__

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;

Respected Advisor
Posts: 3,799

csv file with negative sign behind value

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.

Frequent Contributor
Posts: 139

Re: csv file with negative sign behind value

Can you provide the log pls?

Respected Advisor
Posts: 3,799

Re: csv file with negative sign behind value

Posted in reply to manojinpec

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.

Occasional Contributor
Posts: 7

Re: csv file with negative sign behind value

Posted in reply to data_null__

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;

Solution
‎07-02-2011 04:44 PM
Respected Advisor
Posts: 3,799

Re: csv file with negative sign behind value

    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;

Occasional Contributor
Posts: 7

Re: csv file with negative sign behind value

Posted in reply to data_null__

Thanks!

Occasional Contributor
Posts: 7

Re: csv file with negative sign behind value

Posted in reply to data_null__

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.

Super User
Posts: 10,028

Re: csv file with negative sign behind value

Hi. Dave

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

Thanks.

Ksharp

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1596 views
  • 0 likes
  • 4 in conversation