topic Re: Trying to extract year, etc. from numeric/date in SAS Enterprise Guide

Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 15:05:39 GMT

I'm trying to do the following extracts. I successfully converted tmonth to date/numeric so it's reading as
202201, for example. But I am having a hard time extracting year, yr, quarter, inc_mnth and month2 - these are showing blanks
in the data.

data tab ;
set bat;


format tmonth yyyymm6.;
tmonth=input(put(YrMth,6.),yyyymm6.);

year = year(tmonth);
Quarter = "Q" || put(QTR(tmonth),$1.); 
yr = put (year,z4.); 
inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
month2=put(put(month(tmonth),z2.), $mth.);

run;

Re: Trying to extract year, etc. from numeric/date

Reeza — Thu, 25 Apr 2024 15:17:51 GMT

Can you show your log?

You do have some logical issues, though, what does Yrmth look like and what is the format/type?

Re: Trying to extract year, etc. from numeric/date

yabwon — Thu, 25 Apr 2024 15:34:48 GMT

I you have a valid SAS date variable this could do the job:

data test;

  /* have */
  d = '25apr2024'd;
  put d= / d= date11.;

  /* want */
  y = year(d);
  m = month(d);

  q_character = put(d, QTR.);
  q_numeric = qtr(d);

  yq = put(d, YYQ.);

  ym_character = put(d, YYMMN.);

  ym_numeric = y*100 + m;

  /* printout*/
  put (_ALL_) (=/);

run;

Bart

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 15:43:50 GMT

This is the log. I was wrong, it didint "successfully" convert because of the note in the log but it still shows yrmonth
format. The format type for YrMth was originally CHAR 200 and it looks like 202102.

(Also , $mth. is just Jan, Feb, mar, etc.)

83    data tab;
84    set bat;
85    
86    
87    format tmonth yyyymm6.;
                            ---------
                            484
88    tmonth =input(put(YrMth,6.),yyyymm6.);
                                         ---------
                                         485
NOTE 484-185: Format YYYYMM was not found or could not be loaded.
NOTE 485-185: Informat YYYYMM was not found or could not be loaded.
89    
90    year = year(tmonth);
91    Quarter = "Q" || put(QTR(tmonth),$1.);
WARNING: Character format specified for the result of a numeric expression.
92    yr = put (year,z4.);
93    inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
94    month2=put(put(month(tmonth),z2.), $mth.);
95    
96    run;
NOTE: Variable YrMth is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1019039 at 90:8    1019039 at 91:22   1019039 at 93:16   1019039 at 93:49   1019039 at 94:16   
NOTE: There were 1019039 observations read from the data set WORK.bat.
NOTE: The data set WORK.bat has 1019039 observations and 12 variables.
NOTE: Compressing data set WORK.tab decreased size by 41.12 percent. 
      Compressed is 1101 pages; un-compressed would require 1870 pages.
NOTE: DATA statement used (Total process time):
      real time           1.29 seconds
      cpu time            0.99 seconds

Re: Trying to extract year, etc. from numeric/date

yabwon — Thu, 25 Apr 2024 15:42:30 GMT

You missed the semicolon...

Remember SAS stands for "Semicolon, Always Semicolon"

Bart

Re: Trying to extract year, etc. from numeric/date

ballardw — Thu, 25 Apr 2024 15:42:46 GMT

Did you read your log?

When I create a single value for Yrmth and attempt some of your code there are immediate problems unless your organization has custom format and informat defined for yyyymmn.

You don't provided any definition of the $mth. format so can't tell how to "fix" that call

251  data example;
252     yrmth=202404;
253
254     format tmonth yyyymm6.;
                      --------
                      484
255     tmonth=input(put(YrMth,6.),yyyymm6.);
                                   --------
                                   485
NOTE 484-185: Format YYYYMM was not found or could not be loaded.

NOTE 485-185: Informat YYYYMM was not found or could not be loaded.

256
257  run;

Perhaps this will help:

data example;
   yrmth=202201;

   format tmonth yymm6.;
   tmonth=input(put(YrMth,6.),yymmn6.);
   year = year(tmonth);
   Quarter = "Q" || put(QTR(tmonth),1.); 
   yr = put (year,z4.); 
   inc_mnth = put(tmonth,yymmn6.);
   /*or*/
   inc_month2 = put(yrmth,f6.);
   month2=put(tmonth,monname.);
run;

Note that I am a bit concerned about the rationale for creating all of those character valued "date" bits. Those are generally not as flexible as a numeric date value.

I used the MONNAME format as a guess for the replacement of the $mth. format not supplied.

Also you may want to be aware that Proc Format with the PICTURE statement will let make your own custom formats so you could do something really useless like display the Year, week, quarter, weekday number and name, month number and name all with one (ugly) format and have some of the "number" type values preceded with 0, blank or nothing leading.

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 15:45:40 GMT

I tried yymm6. before and it didn't come through; same with yymmn6.

Re: Trying to extract year, etc. from numeric/date

yabwon — Thu, 25 Apr 2024 15:54:20 GMT

Look into this documentation page: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/n0c9zxlm4e6m7tn1vrn76c98zw66.htm

It will help you.

Bart

Re: Trying to extract year, etc. from numeric/date

ballardw — Thu, 25 Apr 2024 15:57:08 GMT

@bhca60 wrote:
I tried yymm6. before and it didn't come through; same with yymmn6.

Since your example code didn't have a semicolon for the SET statement I have to assume that you had a similar problem with what ever you were trying with the various formats/informats. So the step failed before ever considering them.

Show YOUR log when you have a question about what the log means when it shows things you don't understand.

And please do not say "doesn't work" without attaching a log showing it not "not working". The first thing we will ask for is the log to show what is going on.

Believe it or not, SAS logs are actually helpful. It just takes a bit of experience to understand them.

And you should always check your log. It is not uncommon for someone to say "something isn't working" because values aren't changing when code is changed. Then we find in error in the code that meant the data set isn't replaced. SAS protects you to some extent by not deleting sets when you have errors.

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 16:17:04 GMT

the missing semicolon was just copy/paste error.

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 16:13:44 GMT

the log didnt say anything more than what is shown in the log I posted.

Re: Trying to extract year, etc. from numeric/date

jebjur — Thu, 25 Apr 2024 16:17:14 GMT

Converting a character string of '202201' to numeric is still not a SAS date value, so you won't be able to extract the year, quarter, etc. A SAS date is the number of days since January 1, 1960. In order to create a true SAS date, you can use the MDY function, along with the SUBSTR and PUT functions to parse out the year and month and convert to numeric, and then add a 'day' value of 1 for example:

data tab ;
YrMth='201202';
tmonth=MDY(put(substr(YrMth,5),2.),1,put(substr(YrMth,1,4),4.)); /* create a numeric SAS date value */

year = year(tmonth);
Quarter = "Q" || put(QTR(tmonth),1.);
yr = put (year,z4.);
inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
month2=put(put(month(tmonth),z2.), $mth.);
format tmonth yymm6.;
run;

Does that help?

Re: Trying to extract year, etc. from numeric/date

jebjur — Thu, 25 Apr 2024 16:21:11 GMT

Sorry, meant to say along with the SUBSTR and INPUT functions:
data tab ;
YrMth='201202';
tmonth=MDY(input(substr(YrMth,5),2.),1,input(substr(YrMth,1,4),4.));
year = year(tmonth);
Quarter = "Q" || put(QTR(tmonth),1.);
yr = put (year,z4.);
inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
*month2=put(put(month(tmonth),z2.), $mth.);
format tmonth yymm6.;
run;

Re: Trying to extract year, etc. from numeric/date

ballardw — Thu, 25 Apr 2024 16:28:06 GMT

If you mean this log:

83    data tab;
84    set bat;
85    
86    
87    format tmonth yyyymm6.;
                            ---------
                            484
88    tmonth =input(put(YrMth,6.),yyyymm6.);
                                         ---------
                                         485
NOTE 484-185: Format YYYYMM was not found or could not be loaded.
NOTE 485-185: Informat YYYYMM was not found or could not be loaded.
89    
90    year = year(tmonth);
91    Quarter = "Q" || put(QTR(tmonth),$1.);
WARNING: Character format specified for the result of a numeric expression.
92    yr = put (year,z4.);
93    inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
94    month2=put(put(month(tmonth),z2.), $mth.);
95    
96    run;
NOTE: Variable YrMth is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1019039 at 90:8    1019039 at 91:22   1019039 at 93:16   1019039 at 93:49   1019039 at 94:16   
NOTE: There were 1019039 observations read from the data set WORK.bat.
NOTE: The data set WORK.bat has 1019039 observations and 12 variables.
NOTE: Compressing data set WORK.tab decreased size by 41.12 percent. 
      Compressed is 1101 pages; un-compressed would require 1870 pages.
NOTE: DATA statement used (Total process time):
      real time           1.29 seconds
      cpu time            0.99 seconds

That log shows multiple problems. The biggest is this:

NOTE: Variable YrMth is uninitialized.

That means that there is NEVER a value of YrMth to work with. So everything fails.

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1019039 at 90:8    1019039 at 91:22   1019039 at 93:16   1019039 at 93:49   1019039 at 94:16   
NOTE: There were 1019039 observations read from the data set WORK.bat.

See that note about missing values generated? When it says 1019039 at 90:8 that means line 90, or

90    year = year(tmonth);

had a missing result 1019039 times. Since there are 1019039 observations in the data set that means every one of those Year values are missing. And others. Because the data set Work.bat did not have any values of YrMth.

So you apparently need to fix Work.BAT before attempting this code.

It also shows use of formats and informats that do not exist: YYYYMM or YYYYMMN are not provided by SAS. All of those format and informats use 2 Y's not 4. YYMM or YYMMN.

Use a non-existing Informat an the result in SAS guessing an appropriate format and for numbers that usually means a best. So Tmonth would not be a date value but would look exactly like Yrmth (if a value was provided). Because of the format you attempted to use, if it didn't exist, you might think that it "worked" because 202201 would still look like 202201, but not a date.

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 16:36:09 GMT

THANK YOU!!!

Re: Trying to extract year, etc. from numeric/date

ballardw — Thu, 25 Apr 2024 16:52:06 GMT

Way too much work on the tmonth (as long as the first day of the month is acceptable)

tmonth=input(YrMth,yymmn6.);

if the Yrmth is character, which is not what the OP said at the start of this.

@jebjur wrote:

Converting a character string of '202201' to numeric is still not a SAS date value, so you won't be able to extract the year, quarter, etc. A SAS date is the number of days since January 1, 1960. In order to create a true SAS date, you can use the MDY function, along with the SUBSTR and PUT functions to parse out the year and month and convert to numeric, and then add a 'day' value of 1 for example:

data tab ;
YrMth='201202';
tmonth=MDY(put(substr(YrMth,5),2.),1,put(substr(YrMth,1,4),4.)); /* create a numeric SAS date value */

year = year(tmonth);
Quarter = "Q" || put(QTR(tmonth),1.);
yr = put (year,z4.);
inc_mnth = put(year(tmonth),z4.) || put(month(tmonth),z2.);
month2=put(put(month(tmonth),z2.), $mth.);
format tmonth yymm6.;
run;

Does that help?

Re: Trying to extract year, etc. from numeric/date

bhca60 — Thu, 25 Apr 2024 18:49:57 GMT

It was mentioned that YrMth was originallly CHAR 200 in reply to Reeza.