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

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

Benbo123321 — Thu, 27 Nov 2014 17:43:21 GMT

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.

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

PGStats — Thu, 27 Nov 2014 18:22:37 GMT

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.

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

Benbo123321 — Fri, 28 Nov 2014 02:00:34 GMT

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.

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

PGStats — Fri, 28 Nov 2014 02:31:08 GMT

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;

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

Benbo123321 — Sat, 29 Nov 2014 18:14:53 GMT

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

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

PGStats — Sat, 29 Nov 2014 18:34:36 GMT

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