topic Re: Problem on converting character data to numeric data in SAS Programming

Problem on converting character data to numeric data

Anddiy — Wed, 07 Apr 2021 22:41:28 GMT

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?

Re: Problem on converting character data to numeric data

Reeza — Wed, 07 Apr 2021 23:02:33 GMT

@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.

Re: Problem on converting character data to numeric data

ChrisNZ — Wed, 07 Apr 2021 23:11:00 GMT

> 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.

Re: Problem on converting character data to numeric data

Tom — Wed, 07 Apr 2021 23:19:11 GMT

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

Re: Problem on converting character data to numeric data

Anddiy — Wed, 07 Apr 2021 23:30:07 GMT

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....

Re: Problem on converting character data to numeric data

Anddiy — Wed, 07 Apr 2021 23:56:31 GMT

That worked for me, thanks man!

Im change somethings to ajust in my code but works!

Thanks a lot.

Re: Problem on converting character data to numeric data

Reeza — Thu, 08 Apr 2021 15:10:58 GMT

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....