Problem on converting character data to numeric data

[Anddiy]

Hi Guys,

Im having some problems to convert a SAS char to a numeric sas date,

In my project, i have a numeric column named DT_REFER, this column contains the date of my clients in sas DATE9. format ( Example: 19OCT2020), on my way in this project i needed to group by that data in a month format, so to do group by with data in sas i needed convert that DATE9. to a CHARACTER month format , i did it using a simple put function "PUT(DT_REFER, MMYYD.) AS DT_MONTH " on a proc sql with group by, now the original 19OCT2020 data has been converted to a character "10-2020" and the group by works normaly.

But now i need to convert it back to a date format to send it to Teradata Database, and im having trouble to convert it back, i tried to do some input functions in a data step like that:

DATA CONVERT_BACK;

SET mybase;

DATA_BACK = input(DT_MONTH, MMYYD.);

RUN; 

I want to get a 10-2020 in numeric date format, but all i get is a empty DATA_BACK with a "." in it. 

What i need to do to solve this?  

[Reeza]

---

@Anddiy wrote:

Hi Guys,

Im having some problems to convert a SAS char to a numeric sas date,

In my project, i have a numeric column named DT_REFER, this column contains the date of my clients in sas DATE9. format ( Example: 19OCT2020), on my way in this project i needed to group by that data in a month format, so to do group by with data in sas i needed convert that DATE9. to a CHARACTER month format , i did it using a simple put function "PUT(DT_REFER, MMYYD.) AS DT_MONTH " on a proc sql with group by, now the original 19OCT2020 data has been converted to a character "10-2020" and the group by works normaly.

But now i need to convert it back to a date format to send it to Teradata Database, and im having trouble to convert it back, i tried to do some input functions in a data step like that:

DATA CONVERT_BACK;

SET mybase;

DATA_BACK = input(DT_MONTH, MMYYD.);

RUN; 

I want to get a 10-2020 in numeric date format, but all i get is a empty DATA_BACK with a "." in it. 

What i need to do to solve this?  

---

Regarding the section I've highlighted in orange, that's fortunately wrong. 

You can just format the date and add the GROUPFORMAT option in the BY statement to tell SAS to group the data by the formatted value. 

YYMMN6. is a good format for year/month summary. 

[Anddiy]

I've tried this, and did not work.





CODE:

DATA TEST_FORMAT;
SET mytable;
FORMAT DT_REFER YYMMN6.;
BY GROUPFORMAT DT_REFER;
RUN;

In this case the group by didin't work, it is because if you just "format" a SAS DATE9. (Example: 19OCT2020) the sas will show you 202010, but for sas it's still 19OCT2020, because of that i have created a CHARACTER var for that and format that var to receive 202011....

[Reeza]

Sorry dude, you still did it wrong and it definitely works.

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data want;
set stocks;
where stock = "IBM";
by date groupformat;
format date year4.;
if first.date then flag=1;
if last.date then flag=1;

run;

proc print data=want;run;

---

@Anddiy wrote:
I've tried this, and did not work.





CODE:

DATA TEST_FORMAT;
SET mytable;
FORMAT DT_REFER YYMMN6.;
BY GROUPFORMAT DT_REFER;
RUN;

In this case the group by didin't work, it is because if you just "format" a SAS DATE9. (Example: 19OCT2020) the sas will show you 202010, but for sas it's still 19OCT2020, because of that i have created a CHARACTER var for that and format that var to receive 202011....

---

[ChrisNZ]

> i needed to group by that data in a month format

You don't need to create a new variable for this. Just apply a format to the date as you do the calculation.

[Tom · Accepted solution]

This works:

data test;
  date = '19OCT2020'd ;
  mmyy = put(date,mmyyd7.);
  date2 = input('01-'||mmyy,ddmmyy10.);
  put (_all_) (=);
  format date: date9.;
run;

date=19OCT2020 mmyy=10-2020 date2=01OCT2020

What type of BY statement are you using where you cannot use formatted values to group?

Also why would you use a format that will not sort in the right order?  If you use that format 01-2021 will sort before 02-2020.

760   data test;
761     date = '19OCT2020'd ;
762     yymm = put(date,yymmd7.);
763     date2 = input(cats(yymm,'-01'),yymmdd10.);
764     put (_all_) (=);
765     format date: date9.;
766   run;

date=19OCT2020 yymm=2020-10 date2=01OCT2020

[Anddiy]

That worked for me, thanks man!

Im change somethings to ajust in my code but works!

Thanks a lot.