Help using Base SAS procedures

problem with dates

Accepted Solution Solved
Reply
Super Contributor
Posts: 265
Accepted Solution

problem with dates

Hi all,

I have a date variable where date is stored in number  as 20140202 and there few missing value for the variable .

i want to change it in date9. format. but i m facing error i believe its coming due to missing values for few observation.

Please suggest i tried : here date_hoc , visit1-visit5 is in number and value are as 20140202

 

proc sql;

create table Report as

select      REGION,

            CONTACT_SALSA_ID,

            ORG_NAME,

            customer_name,

            STAGE,

            Put(mdy(input(substr(strip(put(DATE_HOC,$10.)),5,2),2.),input(substr(strip(put(DATE_HOC,$10.)),7),2.),input(substr(strip(put(DATE_HOC,$10.)),1,4),4.)),date9.) as Date_Hoc,

            Product_dicussed_by_HOC,

            SALESFORCE_Hoc,

            Evtype_Hoc,

            Put(mdy(input(substr(strip(put(VISIT1,$10.)),5,2),2.),input(substr(strip(put(VISIT1,$10.)),7),2.),input(substr(strip(put(VISIT1,$10.)),1,4),4.)),date9.) as VISIT1,

            Team1,

            event1,

            products1,

            Put(mdy(input(substr(strip(put(VISIT2,$10.)),5,2),2.),input(substr(strip(put(VISIT2,$10.)),7),2.),input(substr(strip(put(VISIT2,$10.)),1,4),4.)),date9.) as VISIT2,

            Team2,

            Event2,

            Products2,

            Put(mdy(input(substr(strip(put(VISIT3,$10.)),5,2),2.),input(substr(strip(put(VISIT3,$10.)),7),2.),input(substr(strip(put(VISIT3,$10.)),1,4),4.)),date9.) as VISIT3,

            Team3,

            Event3,

            Products3,

            Put(mdy(input(substr(strip(put(VISIT4,$10.)),5,2),2.),input(substr(strip(put(VISIT4,$10.)),7),2.),input(substr(strip(put(VISIT4,$10.)),1,4),4.)),date9.) as VISIT4,

            Team4,

            Event4,

            Products4,

            Put(mdy(input(substr(strip(put(VISIT5,$10.)),5,2),2.),input(substr(strip(put(VISIT5,$10.)),7),2.),input(substr(strip(put(VISIT5,$10.)),1,4),4.)),date9.) as VISIT5,

            Team5,

            Event5,

            Products5

from final

order by org_id ,indi_id;

quit;


Accepted Solutions
Solution
‎04-15-2015 02:45 PM
Super User
Posts: 17,784

Re: problem with dates

Are you getting Warnings or Errors - it really helps if you post the log, what you have and what you expect. Saves time all around.

Does either of the following work for you?

data have;

input date_number;

cards;

20150102

20150131

20150201

20150215

20150228

20150320

.

.

20150401

;

run;

data want;

set have;

date_sas=input(put(date_number, 8. -l), yymmdd8.);

format date_sas date9.;

run;

OR

data want2;

set have;

if date_number=. then date_sas=.;

else date_sas=input(put(date_number, 8. -l), yymmdd8.);

format date_sas date9.;

run;

View solution in original post


All Replies
Super User
Posts: 17,784

Re: problem with dates

What's the error?

Super User
Super User
Posts: 7,392

Re: problem with dates

Agree with Reeza.  I would also add, if you have dates with missing information, then you need to define a set of rules to handle missing.  For instance if day is missing do you want to populate it with a fixed point, e.g. 01, or will it be missing in the output variable.  You then need to code for this.  So:

data have;

  dte="20140105"; output;

  dte="201506"; output;

run;

proc sql;

  create table WANT as

  select  DTE as ORIGINAL_VALUE,

          case  when length(strip(DTE))=8 then input(DTE,yymmdd8.)

                else . end as EXCLUDE_PARTIALS format=date9.,

          case  when length(strip(DTE))=8 then input(DTE,yymmdd8.)

                when length(strip(DTE))=6 then input(strip(DTE)||"01",yymmdd8.)

                else . end as INCLUDE_PARTIALS format=date9.

  from    HAVE;

quit;

Super User
Posts: 10,483

Re: problem with dates

Do you really go through all that to create another text variable that isn't going sort very well?

I would suggest creating the variable as a SAS Date variable and apply the format Date9. Then if I need to do anything with that variable I can get much done but grouping with a different format or change the display by just changing the format at time of display.

Regular Contributor
Posts: 180

Re: problem with dates

IMO you should not have to do this "complex" transformation from a text field to another text field if you use SAS Dates with formats:

From text to text (your solution):

data final;

  date_hoc="20140202";    /* text field */

run;

proc sql;

create table Report as

select

  Put(mdy(input(substr(strip(put(DATE_HOC,$10.)),5,2),2.),input(substr(strip(put(DATE_HOC,$10.)),7),2.),input(substr(strip(put(DATE_HOC,$10.)),1,4),4.)),date9.) as Date_Hoc

from final;

quit;

From numeric field to SAS Date (number) - my suggestion :

data final;

  date_hoc=20140202;  /* numeric field */

run;

proc sql;

create table Report as

select

  input(DATE_HOC,yymmdd8.) as Date_Hoc format date9.   /* SAS date */

from final;

quit;

The advantage of a SAS Date is that you can display it in many ways using different formats and also you can get more information from the date using functions as YEAR, MONTH, DAY, etc.

CTorres

Super Contributor
Posts: 265

Re: problem with dates

Thanks to all.

But i think i was not clear with my que.

Let me clear once again.

I have 2 dataset where where date variable is in simple number. i.e. 20140202 , its not a date format its in number.

After mapping both dataset with the help of combination of variable where date is one of them.

Now i want result output where i want to show date variable in date9. Format.

In simplyfy i want to convert number in date9. Format. Using above mention combination of functions i m getting my result but when ever observation comes where date variable is missing , its throw error.

At the same time i m finding my solution is not standard and realiable.

Kindly suggest.

Super User
Super User
Posts: 7,392

Re: problem with dates

And did you try my code, the case statements, which are there to convert the text field into numeric based on lengths?

          case  when length(strip(DTE))=8 then input(DTE,yymmdd8.)

                else . end as EXCLUDE_PARTIALS format=date9.,

          case  when length(strip(DTE))=8 then input(DTE,yymmdd8.)

                when length(strip(DTE))=6 then input(strip(DTE)||"01",yymmdd8.)

                else . end as INCLUDE_PARTIALS format=date9.

Also, good to post any errors/warnings log parts you get as we don't know what the error your getting is.

Super User
Super User
Posts: 6,499

Re: problem with dates

You can use CASE statement in SQL to trap the missing values are prevent the warning messages.  Also do not try to apply a character format ($10.) to a numeric variable.

case when date_hoc is null then null else input(put(date_hoc,8.),yymmdd8.) end as date_hoc format=date9.

Solution
‎04-15-2015 02:45 PM
Super User
Posts: 17,784

Re: problem with dates

Are you getting Warnings or Errors - it really helps if you post the log, what you have and what you expect. Saves time all around.

Does either of the following work for you?

data have;

input date_number;

cards;

20150102

20150131

20150201

20150215

20150228

20150320

.

.

20150401

;

run;

data want;

set have;

date_sas=input(put(date_number, 8. -l), yymmdd8.);

format date_sas date9.;

run;

OR

data want2;

set have;

if date_number=. then date_sas=.;

else date_sas=input(put(date_number, 8. -l), yymmdd8.);

format date_sas date9.;

run;

Super Contributor
Posts: 265

Re: problem with dates

Hi Reezam its working but i had to remove "-I" , with "-I" its not working , Can u please suggest me 

Super User
Posts: 17,784

Re: problem with dates

Did you put a comma between the 8. and -l? There shouldn't be one.

It's probably not even necessary as long as all your dates are exactly 8 digits, just a happen.

The -l is an optional parameter in the put statement that aligns the variable, left alignment in this case. Check the docs for more information.

Occasional Contributor
Posts: 17

Re: problem with dates

Hi,

I guess you should convert the date variables to the appropriate format (Say Date9.) before merging the both datasets and then go for merging. Hope this will resolve your issue.

Regards,

Yogesh

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 540 views
  • 0 likes
  • 7 in conversation