topic Re: Text to colomn the date in SAS Programming

Text to colomn the date

Rajeshganta — Wed, 26 Feb 2020 14:00:57 GMT

I have a data from external source as dd-mm-yyyy when i delimit in excel it gets converted to mm/dd/yyyy which is syatem date format how can i use this step in sas

Re: Text to colomn the date

Kurt_Bremser — Wed, 26 Feb 2020 14:10:52 GMT

What is the format of the external data? Do you pull directly from a database, or read from a text file?

To display a date value (count of days) in the way you want, use the ddmmyyd10. format.

How date values are displayed in Excel is mostly influenced by Excel settings, not by SAS.

Re: Text to colomn the date

Tom — Wed, 26 Feb 2020 14:12:11 GMT

You need to provide more details about what the issue is.

If you are using SAS to read from a text file then make sure to use the appropriate INFORMAT to convert the text into dates. You can then attach any date type format to the variable to control how the dates are displayed in SAS.

If you have an Excel file with dates and use SAS to convert that into a SAS dataset it should recognize them as dates. Just make sure that ALL of the cells in the column of the spreadsheet are dates.

If you have question on how to get Excel to read the text file properly then this might not be the right forum.

Re: Text to colomn the date

Rajeshganta — Thu, 27 Feb 2020 00:50:19 GMT

Data is pulled from database and the date format i m getting is in character which is like
27-02-2020
28-02-2020
29-02-2020
But the date is not recognised by sas as per requirement i have to get the data of yesterday with formula of today()-1 so please help me

Re: Text to colomn the date

Kurt_Bremser — Thu, 27 Feb 2020 06:18:38 GMT

Please show the SAS code you use to import the data. Use the "little running man" icon to post this code.

To convert character values to dates, do this:

data have;
input mydate $10.;
datalines;
27-02-2020
28-02-2020
29-02-2020
;

data want;
set have (rename=(mydate=_mydate));
mydate = input(_mydate,ddmmyy10.);
format mydate e8601da10.;
drop _mydate;
run;

proc print data=want noobs;
run;

Result:

mydate

2020-02-27
2020-02-28
2020-02-29

It should be possible to incorporate the conversion in your import step.

Re: Text to colomn the date

Patrick — Thu, 27 Feb 2020 07:08:58 GMT

@Rajeshganta wrote:
Data is pulled from database and the date format i m getting is in character which is like
27-02-2020
28-02-2020
29-02-2020
But the date is not recognised by sas as per requirement i have to get the data of yesterday with formula of today()-1 so please help me

If the column in the database is of type DATE (or similar) then SAS gets things normally right and creates a SAS Date value stored in a SAS numerical variable. So... are you sure that the variable on the SAS side is of type character? Or is it a numerical variable and just has a date format applied other than what you'd like it to be.

Below code should give you the pointers for both how to change the format or how to convert a string to a SAS date value.

data have;
infile datalines truncover;
input 
  @1 date_as_string $10.
  @1 date_as_SASDateValue ddmmyy10.;

  /* 
     date_as_SASDateValue contains the count of days since 1/1/1960
     applying SAS formats on such values prints the date human readable (does NOT change the value).
     docu here:
     https://go.documentation.sas.com/?docsetId=leforinforref&docsetTarget=n0p2fmevfgj470n17h4k9f27qjag.htm&docsetVersion=9.4&locale=en
  */
  format SASDateFormatted_1 ddmmyyD10.;
  SASDateFormatted_1=date_as_SASDateValue;

  format SASDateFormatted_2 ddmmyy10.;
  SASDateFormatted_2=date_as_SASDateValue;

  /* here how to convert a string representing a date to a SAS date value */
  format SASDateFormatted_3 date9.;
  SASDateFormatted_3 = input(date_as_string,ddmmyy10.);

  /* because SAS date values are simply a count of days one can use this
     data for calculations. Here how to substract a day
  */
  format SASDateFormatted_4 b8601da.;
  SASDateFormatted_4 = date_as_SASDateValue -1;

datalines;
27-02-2020
28-02-2020
29-02-2020
;

proc print data=have;
run;

When writing a SAS table to a database (or Excel) then I've made the experience that things work best if you apply a format of DATE9 to variable with SAS date values and a format of DATETIME20 to variables with SAS datetime values.

Re: Text to colomn the date

Rajeshganta — Wed, 11 Mar 2020 18:29:20 GMT

Thank you Mr.KURT BREMSER
Can you please suggest me in format for data like
Mm/dd/yyyy
03/11/2020
03/12/2020
03/13/2020

Re: Text to colomn the date

Kurt_Bremser — Wed, 11 Mar 2020 18:59:26 GMT

Maxim 1: Read the Documentation.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=leforinforref&docsetTarget=n0verk17pchh4vn1akrrv0b5w3r0.htm&locale=en

Look under Date and Time for the fitting informat.