DATA Step, Macro, Functions and more

formatting dates

Reply
Contributor
Posts: 46

formatting dates

Hi,

I posted this question before but this time I'm getting a different solution when I tried to convert date to mmddyy10. format.

The dataset has a varaible "createddate" as 20150211 and I'm trying to change it to 02/11/2015 format. Createddate is a numeric var. I tried to convert it to char first and then format to mmddyy10. But it is not working moreover it's giving missing values and the dates are going back to 2002 rather than 2015.

 

Here's my code:

data new;

set raw;

createdon=put(createdon,z8.);

created=input(createdon,yymmdd8.);

format createdon mmddyy10.;

drop createdon1;

run;

 

I also tried nesting the input and put function as I did from the previous post.

created0n=input(put(createdon, z8.),?? yymmdd8.);

 

but both ways it is generating missing values and is going back to 2002 date which is not in the dataset. The dataset has only 2015 dates.

One of the error message is:

NOTE: Invalid numeric data, '03/09/2015' , at line 3153 column 11.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

createdon=. filecreatedate=20150316 soldto_customernumber=10045343 shipto_customernumber=.

salesdocnumber=108473239 salesitem=50 dtcnumber=10045343 ordermethod=ZPHO createdby=ET26EO4

pono=ATTN STACEY PREPARE TONER orderreason= reason_rejected=

itemdescription=Obagi Tretinoin 0.05% Cream 20G 1/ea qty_ordered=12.000 uom=EA freegoods=N

del_block= shipto_address1=5005 ROCKSIDE RD #1225 shipto_address2= shipto_city=INDEPENDENCE

shipto_state=OH shipto_postalcode=44131 soldto_customername=NICHOLAS HUSNI MD INC

referencedocnumber=. referenceitemnumber=0 doctype=ZDOR itemcategory=ZDTN extendedprice=498

net_sell_price=473.16 discountamt=24.84 discountpct=4.99 unitprice=41.5 shipto_customername=

item_no=5005005 prod_lin=TRETINOIN prod_fam=TRETINOIN prod_cat=RX prod_grp=TRET createdon1=.

_ERROR_=1 _N_=20

 

And the other is

NOTE: Invalid argument to function INPUT at line 3136 column 9.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

createdon=20150309 filecreatedate=20150316 soldto_customernumber=10045343

shipto_customernumber=. salesdocnumber=108473239 salesitem=50 dtcnumber=10045343

ordermethod=ZPHO createdby=ET26EO4 pono=ATTN STACEY PREPARE TONER orderreason=

reason_rejected= itemdescription=Obagi Tretinoin 0.05% Cream 20G 1/ea qty_ordered=12.000

uom=EA freegoods=N del_block= shipto_address1=5005 ROCKSIDE RD #1225 shipto_address2=

shipto_city=INDEPENDENCE shipto_state=OH shipto_postalcode=44131

soldto_customername=NICHOLAS HUSNI MD INC referencedocnumber=. referenceitemnumber=0

doctype=ZDOR itemcategory=ZDTN extendedprice=498 net_sell_price=473.16 discountamt=24.84

discountpct=4.99 unitprice=41.5 shipto_customername= item_no=5005005 prod_lin=TRETINOIN

prod_fam=TRETINOIN prod_cat=RX prod_grp=TRET createdon1=. created=. _ERROR_=1 _N_=20

NOTE: Mathematical operations could not be performed at the following places. The results of

the operations have been set to missing values.

Each place is given by: (Number of times) at (Line)Smiley SadColumn).

764004 at 3136:9

 

Thank you

Malathi

Super User
Posts: 7,866

Re: formatting dates

Posted in reply to Malathi13

createdon is the result of a put function and therefore of type character; you can`t assign a date format (which is numeric) to it.

Your codepiece should probably look like that:

createdon1=put(createdon,z8.);
createdon=input(createdon1,yymmdd8.);
format createdon mmddyy10.;
drop createdon1;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 46

Re: formatting dates

Posted in reply to KurtBremser

Hi Kurt,

I tried that code but still it is giving me missing values and the value of the date is 2002. I don't understand when the dataset has only 2015 data why it is showing me 2002? That's so weird date I'm getting.

 

Malathi

Super User
Posts: 19,878

Re: formatting dates

Posted in reply to Malathi13

You say the original variable is numeric, what format is attached to the date variable? If you say not working, please post the exact code you used and the log.  Ideally, data so we can replicate your question. 

Contributor
Posts: 44

Re: formatting dates

Posted in reply to Malathi13
Use date9. instead of z8.

createdon1=put(createdon,date9.);
Contributor
Posts: 44

Re: formatting dates

Posted in reply to anoopmohandas7

A sample code in case u want to test.

 

data have;
input createdon yymmdd8. ;
datalines ;
20150211
run ;

data new ;
set have ;
createdon1=put(createdon,date8.);
createdon=input(createdon1,anydtdte15.);
format createdon mmddyy10. ;
drop createdon1;
run;

proc print data=new;run ;
Contributor
Posts: 46

Re: formatting dates

Posted in reply to anoopmohandas7

Thank you so so much Anoopmohandas7. It worked perfectly without creating a new varaible and with no missing values. Also the date is in 2015 not 2002.

 

Thanks again

Malathi

Super User
Posts: 11,343

Re: formatting dates

Posted in reply to Malathi13

Malathi13 wrote:

Hi Kurt,

I tried that code but still it is giving me missing values and the value of the date is 2002. I don't understand when the dataset has only 2015 data why it is showing me 2002? That's so weird date I'm getting.

 

Malathi


Show the code and the log. And at least one orginal value that results in 2002 from 2015.

Super User
Posts: 5,518

Re: formatting dates

Posted in reply to Malathi13

It's a little bit of a mismatch.  You have a numeric variable createddate, but your code doesn't refer to that in any way.  Using that name, and assuming it is a numeric variable that needs to be converted to a date, these statements would do the trick:

 

createddate = input( put(createddate,z8.), yymmdd8.);

format createddate mmddyys10.;

 

Contributor
Posts: 46

Re: formatting dates

Posted in reply to Astounding

in the case of missing values please have a look into, the place from which the column is getting imported from and applay the format at that level for avoiding any confusion.

 

SO at that level itself add:

 

input ( put (DateCol, Z8.) , yymmdd8.);

Ask a Question
Discussion stats
  • 9 replies
  • 164 views
  • 0 likes
  • 7 in conversation