Date Informat not found

[crawfe]

Hi. I am new to DI Studio (4.902). I transferred some code from EG which involves the mmyys7. format, since I

am grouping data by month/year.

I keep getting the "informat MMYYS was not found or could not be located" error. My previous Extract/Join steps

all used this format successfully. I am not sure why User Written Code would be different? The informat is not in the 

user code at all (I have included the failing code and some of the built-in code below).

Thanks for any advice.

1801
1802 data FILL_IN_ALL_DATEROWS(drop=rc);
1803
1804 declare hash h(dataset:'TEMP_ALLTABLE');
1805 h.definekey( 'COUNTRY_NAME', 'CONNECT_MONTH' );
1806 h.definedata('COUNT_CONNECT_MONTHS_COUNTRY');
1807 h.definedone();
1808
1809 set TEMP_ALLTABLE ;/*set WORK.JOIN_UPNUMDEFS_TC_LIVESN;*/
1810 by COUNTRY_NAME CONNECT_MONTH;
1811
1812 if first.COUNTRY_NAME then do;
1813 CONNECT_MONTH='01oct2017'd; /* starting MY */
1814 do until (CONNECT_MONTH=intnx('month', today(), 0, 'b')); /* was -1 */
1815 COUNT_CONNECT_MONTHS_COUNTRY=0;
1816 rc=h.find();
1817 output;
1818 CONNECT_MONTH=intnx('month', CONNECT_MONTH, 1, 'b');
1819 end;
1820 end;
1821 /* format CONNECT_MONTH mmyys7.;*/ same with or without commenting out......
1822 run;

ERROR: The informat MMYYS was not found or could not be loaded.

1709 %let _OUTPUT_col2_name = "CONNECT_MONTH"n;
1710 %let _OUTPUT_col2_table = work."W7RQJ432"n;
1711 %let _OUTPUT_col2_length = 8;
1712 %let _OUTPUT_col2_type = ;
1713 %let _OUTPUT_col2_format = mmyys7.;
1714 %let _OUTPUT_col2_informat = mmyys7.;
1715 %let _OUTPUT_col2_label = %nrquote(CONNECT_MONTH);
1716 %let _OUTPUT_col2_input0 = "CONNECT_MONTH"n;
1717 %let _OUTPUT_col2_exp = ;
1718 %let _OUTPUT_col2_input = "CONNECT_MONTH"n;
1719 %let _OUTPUT_col2_input_count = 1;

[PaigeMiller]

Informat MMYYS does not exist. I don't know why you state it worked previously, but here is a list of all possible Informats, and there is no MMYYS 

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=allprodslang&docsetTarget=syn...

Format MMYYS does exist, but that doesn't mean you can use it as an Informat.

[PeterClemmensen]

Is this your entire code? And you get the same error when you comment the format statement out?

[crawfe]

Yes I get the same error whether or not that format statement is commented out.

And the "mmyys7." gives me no error in other Join or Extract steps in the Mapping,

either in the format or informat fields. Does that make sense to you?

I do need to group by month/year (able to be sorted as a date) so if its a particular informat sensitivity in User Written Code,

should I take the long way around and

do Month, Year, concatenate, convert mm/yyyy back to Date type?

(I can't believe SAS wouldn't have that particular (in)format :-/).

[PaigeMiller]

---

@crawfe wrote:

Yes I get the same error whether or not that format statement is commented out.

And the "mmyys7." gives me no error in other Join or Extract steps in the Mapping,

either in the format or informat fields. Does that make sense to you?

I do need to group by month/year (able to be sorted as a date) so if its a particular informat sensitivity in User Written Code,

should I take the long way around and

do Month, Year, concatenate, convert mm/yyyy back to Date type?

(I can't believe SAS wouldn't have that particular (in)format :-/).

---

SAS does have the MMYYS format.

It does not have the MMYYS informat.

If you need an informat to read data that appears as mm/yy (example: 02/17), you can use the ANYDTDTE informat.

[crawfe · Accepted solution]

Thanks! I kept the format mmyys7. but used the anydtdte7. informat on both the Source and Target

tables and it worked. (and I improved my Hash code).

[Kurt_Bremser]

The code is syntactically correct, but has a flaw:

37         data temp_alltable;
38         input country_name $ connect_month :date9. count_connect_months_country;
39         format connect_month mmyys7.;
40         datalines;

NOTE: The data set WORK.TEMP_ALLTABLE has 2 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
43         ;

44         
45         data FILL_IN_ALL_DATEROWS(drop=rc);
46         
47         declare hash h(dataset:'TEMP_ALLTABLE');
48         h.definekey( 'COUNTRY_NAME', 'CONNECT_MONTH' );
49         h.definedata('COUNT_CONNECT_MONTHS_COUNTRY');
50         h.definedone();
51         
2                                                          Das SAS System                           07:43 Tuesday, November 19, 2019

52         set TEMP_ALLTABLE ;/*set WORK.JOIN_UPNUMDEFS_TC_LIVESN;*/
53         by COUNTRY_NAME CONNECT_MONTH;
54         
55         if first.COUNTRY_NAME then do;
56           CONNECT_MONTH='01oct2017'd; /* starting MY */
57           do until (CONNECT_MONTH=intnx('month', today(), 0, 'b')); /* was -1 */
58             COUNT_CONNECT_MONTHS_COUNTRY=0;
59             rc=h.find();
60             output;
61             CONNECT_MONTH=intnx('month', CONNECT_MONTH, 1, 'b');
62           end;
63         end;
64         format CONNECT_MONTH mmyys7.;
65         run;

NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: The data set WORK.FILL_IN_ALL_DATEROWS has 50 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

As you can see in the log, the hash object is recreated for every observation, and that causes a memory leak in my experience; you should only create it once at _N_ = 1:

45         data FILL_IN_ALL_DATEROWS(drop=rc);
46         if _N_ = 1
47         then do;
48           declare hash h(dataset:'TEMP_ALLTABLE');
49           h.definekey( 'COUNTRY_NAME', 'CONNECT_MONTH' );
50           h.definedata('COUNT_CONNECT_MONTHS_COUNTRY');
51           h.definedone();
52         end;
53         set TEMP_ALLTABLE ;/*set WORK.JOIN_UPNUMDEFS_TC_LIVESN;*/
54         by COUNTRY_NAME CONNECT_MONTH;
55         if first.COUNTRY_NAME then do;
56           CONNECT_MONTH='01oct2017'd; /* starting MY */
57           do until (CONNECT_MONTH=intnx('month', today(), 0, 'b')); /* was -1 */
58             COUNT_CONNECT_MONTHS_COUNTRY=0;
59             rc=h.find();
60             output;
61             CONNECT_MONTH=intnx('month', CONNECT_MONTH, 1, 'b');
62           end;
63         end;
64         format CONNECT_MONTH mmyys7.;
65         run;

NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: There were 2 observations read from the data set WORK.TEMP_ALLTABLE.
NOTE: The data set WORK.FILL_IN_ALL_DATEROWS has 50 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Now the table is read once to populate the hash, and a second time for the main data step loop.

[crawfe]

Re: the HASH fix; thanks! that code is really at the limit of my experience/knowledge.