BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aman4SAS
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
Reeza
Super User

What's the error?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

ballardw
Super User

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.

CTorres
Quartz | Level 8

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

Aman4SAS
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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.

Reeza
Super User

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;

Aman4SAS
Obsidian | Level 7

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

Reeza
Super User

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.

shubhayog
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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