Help using Base SAS procedures

How do I change the char variable in some datasets (but not all) to a date format?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How do I change the char variable in some datasets (but not all) to a date format?

I'm relatively new to SAS. What I want to do is simple enough, however, every time I try to run the code with minor tweaks, I get an error. I have twelve months worth of information, P1-P12.  For P1-P3, the variable DOC_DATE is in the correct format, MMDDYY10. For P4-P12, DOC_DATE is a char variable. This is keeping me from creating one large data set for the entire year. Therefore, I need to make DOC_DATE in the P4-P12 data sets the same format, MMDDYY10. I do that by creating a temporary variable (_date) to house the correct date, called Document_Date, and then renaming it to DOC_DATE and dropping the extraneous variables.

Code:

  Data DataRequest;

       SET ERPOCT.p1_readin ERPNOV.p2_readin ERPDEC.p3_readin /*these 3 are correctly formatted   */
              ERPJAN.p4_readin(rename=(Document_Date=DOC_DATE)) ERPFEB.p5_readin(rename=(Document_Date=DOC_DATE))
              ERPMAR.p6_readin(rename=(Document_Date=DOC_DATE))  ERPAPR.p7_readin(rename=(Document_Date=DOC_DATE))  ERPMAY.p8_readin(rename=(Document_Date=DOC_DATE))
              ERPJUN.p9_readin(rename=(Document_Date=DOC_DATE))  ERPJUL.p10_readin(rename=(Document_Date=DOC_DATE))
              ERPAUG.p11_readin(rename=(Document_Date=DOC_DATE))  ERPSEP.p12_readin(rename=(Document_Date=DOC_DATE));

            format _date  DOC_DATE  mmddyy10.;   /*_data is temp variable */
           _date=input(compress(Document_Date),MMDDYY10.);  /* remove blank of Document _Date */

            DOC_DATE = _date;     /* retake the DOC_DATE */
            DROP   _Date  Document_date;
      where SUBSTR(Account,1,4) IN ('2110','2112','4221','4222','4801');

RUN;

I get the following Error Message for P4-P12:

ERROR: Variable Document_Date is not on file ERPJAN.P4_READIN

ERROR: Invalid DROP, KEEP, or RENAME OPTION on file ERPJAN.P4_READIN

Any help is appreciated.  Thank you.

M.


Accepted Solutions
Solution
‎11-27-2014 09:31 PM
Respected Advisor
Posts: 4,606

Re: How do I change the char variable in some datasets (but not all) to a date format?

If DOC_DATE really is a SAS date in the first three datasets and a string in the others, then this should work:

Data DataRequest;

       SET      ERPOCT.p1_readin (in=in1)

                 ERPNOV.p2_readin (in=in2)

                 ERPDEC.p3_readin (in=in3)

              ERPJAN.p4_readin(rename=(DOC_DATE=Document_Date))

             ERPFEB.p5_readin(rename=(DOC_DATE=Document_Date))

              ERPMAR.p6_readin(rename=(DOC_DATE=Document_Date)) 

               ERPAPR.p7_readin(rename=(DOC_DATE=Document_Date))      

               ERPMAY.p8_readin(rename=(DOC_DATE=Document_Date))

              ERPJUN.p9_readin(rename=(DOC_DATE=Document_Date)) 

              ERPJUL.p10_readin(rename=(DOC_DATE=Document_Date))

              ERPAUG.p11_readin(rename=(DOC_DATE=Document_Date)) 

               ERPSEP.p12_readin(rename=(DOC_DATE=Document_Date));

            format DOC_DATE  mmddyy10.;

            if not (in1 or in2 or in3) then

                 DOC_DATE = input(compress(Document_Date), MMDDYY10.);

            DROP Document_date;

  /* Comment this out for now

  where SUBSTR(Account,1,4) IN ('2110','2112','4221','4222','4801');  */

RUN;

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,606

Re: How do I change the char variable in some datasets (but not all) to a date format?

Well the first error message says that Document _Date is not a known variable name in ERPJAN.P4_READIN. Either it is misspelled OR ERPJAN.P4_READIN doesn't point where it should.


That's the first thing to fix.


PG

PG
Occasional Contributor
Posts: 13

Re: How do I change the char variable in some datasets (but not all) to a date format?

Updated Code with positions for DOC_DATE and Document_Date switched. 

  Data DataRequest;

       SET ERPOCT.p1_readin ERPNOV.p2_readin ERPDEC.p3_readin /*these 3 are correctly formatted   */

              ERPJAN.p4_readin(rename=(DOC_DATE=Document_Date)) ERPFEB.p5_readin(rename=(DOC_DATE=Document_Date))

              ERPMAR.p6_readin(rename=(DOC_DATE=Document_Date))  ERPAPR.p7_readin(rename=(DOC_DATE=Document_Date))       ERPMAY.p8_readin(rename=(DOC_DATE=Document_Date))

              ERPJUN.p9_readin(rename=(DOC_DATE=Document_Date))  ERPJUL.p10_readin(rename=(DOC_DATE=Document_Date))

              ERPAUG.p11_readin(rename=(DOC_DATE=Document_Date))  ERPSEP.p12_readin(rename=(DOC_DATE=Document_Date));

            format _date  DOC_DATE  mmddyy10.;   /*_data is temp variable */

           _date=input(compress(Document_Date),MMDDYY10.);  /* remove blank of Document _Date */

            DOC_DATE = _date;     /* retake the DOC_DATE */

            DROP   _Date  Document_date;

      where SUBSTR(Account,1,4) IN ('2110','2112','4221','4222','4801');

RUN;

This code runs but with a blank Document_Date column.  I just need the Document_Date variable to be formatted correctly as a date, as it is for P1-P3, and be fully populated.

Solution
‎11-27-2014 09:31 PM
Respected Advisor
Posts: 4,606

Re: How do I change the char variable in some datasets (but not all) to a date format?

If DOC_DATE really is a SAS date in the first three datasets and a string in the others, then this should work:

Data DataRequest;

       SET      ERPOCT.p1_readin (in=in1)

                 ERPNOV.p2_readin (in=in2)

                 ERPDEC.p3_readin (in=in3)

              ERPJAN.p4_readin(rename=(DOC_DATE=Document_Date))

             ERPFEB.p5_readin(rename=(DOC_DATE=Document_Date))

              ERPMAR.p6_readin(rename=(DOC_DATE=Document_Date)) 

               ERPAPR.p7_readin(rename=(DOC_DATE=Document_Date))      

               ERPMAY.p8_readin(rename=(DOC_DATE=Document_Date))

              ERPJUN.p9_readin(rename=(DOC_DATE=Document_Date)) 

              ERPJUL.p10_readin(rename=(DOC_DATE=Document_Date))

              ERPAUG.p11_readin(rename=(DOC_DATE=Document_Date)) 

               ERPSEP.p12_readin(rename=(DOC_DATE=Document_Date));

            format DOC_DATE  mmddyy10.;

            if not (in1 or in2 or in3) then

                 DOC_DATE = input(compress(Document_Date), MMDDYY10.);

            DROP Document_date;

  /* Comment this out for now

  where SUBSTR(Account,1,4) IN ('2110','2112','4221','4222','4801');  */

RUN;

PG

PG
Occasional Contributor
Posts: 13

Re: How do I change the char variable in some datasets (but not all) to a date format?

Thank you that appears to solve the problem for compiling these data sets.  The only "warnings" I get now are this:

WARNING: Multiple lengths were specified for the variable PERIOD by input data set(s). This may cause truncation of data.

WARNING: Multiple lengths were specified for the variable REG_NO by input data set(s). This may cause truncation of data.

NOTE: Invalid argument to function INPUT at line 35 column 18.

What does this usually indicate?

-M

Respected Advisor
Posts: 4,606

Re: How do I change the char variable in some datasets (but not all) to a date format?

The warnings mean that those character variables do not have the same length in every dataset. SAS uses the variable lengths from the first dataset, so if subsequent datasets specify a longer length, values may be truncated. You may circumvent this problem by specifying a length for those variables as the first statement in the datastep (before the set statement).

The note usually results from a string that cannot be translated into a number with the requested informat. You can make SAS generate a missing value without complaining with the ?? notation : myNumber = input(myString, ?? informat.);

PG

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 226 views
  • 6 likes
  • 2 in conversation