topic Re: Convert character "YYYY-MM" to numeric end date in one step in SAS Programming

Convert character "YYYY-MM" to numeric end date in one step

David_Billa — Sat, 22 Aug 2020 04:54:44 GMT

I'm trying to convert character variable to numeric date format in one step, but it's not working. Looking for guidance to resolve the error. I've to do this in one data step as I can able to achieve the result in two data steps.

Code which I tried is,

data want;
    PERIOD_START_MONTH ="2017-03";
    PERIOD_START_MONTH =intnx('month',input(compress(PERIOD_START_MONTH,'-'),yymmn6.),0,"END");
    format PERIOD_START_MONTH date9.;
run;

Log is,

26         data want;
27             PERIOD_START_MONTH ="2017-03";
28             PERIOD_START_MONTH =intnx('month',input(compress(PERIOD_START_MONTH,'-'),yymmn6.),0,"END");
29             format PERIOD_START_MONTH date9.;
                                         ______
                                         484
NOTE 484-185: Format $DATE was not found or could not be loaded.

30         run;

Excepted result is '31MAR2017' to the variable 'PERIOD_START_MONTH'

Re: Convert character "YYYY-MM" to numeric end date in one step

Kurt_Bremser — Sat, 22 Aug 2020 04:36:00 GMT

PERIOD_START_MONTH is character, so you can't use a numeric format. Apply the format to the new variable right.

Re: Convert character "YYYY-MM" to numeric end date in one step

David_Billa — Sat, 22 Aug 2020 04:53:01 GMT

May I know why it's not working if I try to change the datatype and format of the same variable (PERIOD_START_MONTH) in one step? I've to implement this change in one single step.

26         data want;
27             PERIOD_START_MONTH ="2017-03";
28             PERIOD_START_MONTH =intnx('month',input(compress(PERIOD_START_MONTH,'-'),yymmn6.),0,"END");
29             format PERIOD_START_MONTH date9.;
                                         ______
                                         484
NOTE 484-185: Format $DATE was not found or could not be loaded.

30         run;

Re: Convert character "YYYY-MM" to numeric end date in one step

Shmuel — Sat, 22 Aug 2020 06:33:17 GMT

The only way to change variable type is by using a similar method as:

data new;
 set have(rename=(date=new_name)); /* input date is char type */
       date= input(new_name,<date informat>.); /* new date as num type */
    ....
run;

Re: Convert character "YYYY-MM" to numeric end date in one step

David_Billa — Sat, 22 Aug 2020 05:59:21 GMT

Well. I have a different question which is off the topic.

How to convert numeric date value '31MAR2017'd to character value "2017-03"?

Re: Convert character "YYYY-MM" to numeric end date in one step

Kurt_Bremser — Sat, 22 Aug 2020 06:31:00 GMT

@David_Billa wrote:

May I know why it's not working if I try to change the datatype and format of the same variable (PERIOD_START_MONTH) in one step? I've to implement this change in one single step.
26         data want;
27             PERIOD_START_MONTH ="2017-03";
28             PERIOD_START_MONTH =intnx('month',input(compress(PERIOD_START_MONTH,'-'),yymmn6.),0,"END");
29             format PERIOD_START_MONTH date9.;
                                         ______
                                         484
NOTE 484-185: Format $DATE was not found or could not be loaded.

30         run;

Because you simply can't. The variable type is determined once during the compilation phase, and the data step compiler sets up the variable in the PDV according to that. To change the type of a variable, you have to do the rename old/create new/drop old dance.

Re: Convert character "YYYY-MM" to numeric end date in one step

Shmuel — Sat, 22 Aug 2020 06:31:32 GMT

Using format yymms7. will display yyyy/mm then replace the '/' into '-'

data _null_;
   date = '31MAR2017'd;
   year_month = translate(put(date,yymms7.),'-','/');

Re: Convert character "YYYY-MM" to numeric end date in one step

Kurt_Bremser — Sat, 22 Aug 2020 06:37:28 GMT

@David_Billa wrote:
Well. I have a different question which is off the topic.

How to convert numeric date value '31MAR2017'd to character value "2017-03"?

Maxim 1: Read the Documentation.

In this case, Formats by Category. Scroll down to the "Date" category.

Re: Convert character "YYYY-MM" to numeric end date in one step

Shmuel — Sat, 22 Aug 2020 07:08:11 GMT

The documentation for using x if a date format is:

"where the xi n the format name is a character that represents the special character that separates the day, month, and year. The special character can be a blank character, colon (:), hyphen (-),no separator, period (.), or slash (/)."

BUT it doesn't say which character to use in each case.

Mostly it is the first character of it's name except the for hyphen - use 'd' for '-', as in next code:

data _null_;
  dt = today();
  put dt yymmn6.;
  put dt yymms7.; * Slash ;
  put dt yymmp7.; * Point ;
  put dt yymmc7.; * Colon ;
  put dt yymmd7.; * hyphen;
  
run;

Re: Convert character "YYYY-MM" to numeric end date in one step

David_Billa — Sat, 22 Aug 2020 07:39:21 GMT

Yes, but it will just apply the format. But my question is how to change
the numeric variable value '31MAR2017'd to character variable with value
"2017-03"?

Re: Convert character "YYYY-MM" to numeric end date in one step

Kurt_Bremser — Sat, 22 Aug 2020 08:35:48 GMT

@David_Billa wrote:
Yes, but it will just apply the format. But my question is how to change
the numeric variable value '31MAR2017'd to character variable with value
"2017-03"?

Create the new character variable with the PUT function and the wanted format.

Re: Convert character "YYYY-MM" to numeric end date in one step

David_Billa — Sat, 22 Aug 2020 10:37:43 GMT

@Kurt_Bremser @Shmuel I tried the code below. Desired ouput for 'dt_char' is 2017-03

But the Output which I got is 20909 which is not right. I would like to know the Format to get this done. Clearly Format $8. is not right.

data want;
    dt ='31MAR2017'd;
    format dt date9.;
    dt_char=put(dt,$8.);
run;

Re: Convert character "YYYY-MM" to numeric end date in one step

Kurt_Bremser — Sat, 22 Aug 2020 11:43:22 GMT

Maxim 2: Read the Log!

You will find a NOTE because you used a character format for a numeric variable.

And why are you using a format that is not in the Date category, to which I pointed you explicitly?

You will find the format you need in the Date category, but I will not insult your intelligence by spoon-feeding it to you. Instead I'm trying to enable you to solve such simple issues by yourself.

Re: Convert character "YYYY-MM" to numeric end date in one step

Shmuel — Sat, 22 Aug 2020 12:46:45 GMT

you need to understand how SAS deals with dates.

I hope you can run sample codes, just to try and learn.

1) sas date is the number of days since 01JAN1960.

to check it run next code and see the log:

data _null_;
     dt1 = '01JAN1960'd; put dt1=;
     dt2 = '31MAR2017'd; put dt2=;
run;

What have you got in log ?

Does the dt2 value reminds something ? - see your last post.

If you read the whole conversation in this blog you will find different formats to

report a date as a combination of year and month.

pay attention - same format can be used as: informat, put to lug or converting a numeric date variable to a char type variable. Try and run next sample code:

data _null_;
     dt1 = today();
     putlog dt1=  dt1= ddmmyy10.  dt1=yymmd7. ;
     
     dt2= put(dt1,5.); put dt2=;
     dt3 = put(dt1,yymmdds10.); put dt3=;
     dt4 = put(dt1,yymmp7.); put dt4=;
run;

if run above code you probably noticed that the same date DT1 can be displayed/converted

to different formats.

Having @Kurt_Bremser references to documentation (I have learned from it) and previous mine posts, I hope you can choose the proper format to create a char variable from a sas date, as you want.