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;
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;
What's the error?
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;
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.
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
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.
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.
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.
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;
Hi Reezam its working but i had to remove "-I" , with "-I" its not working , Can u please suggest me
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.