Calcite | Level 5

## Query on Lesson 4 - Computing new Columns - Practice level 2

Hi,

I am reading through the solution for Level 2 Practice in Lesson 4 Creating New Columns with Character and Date Functions.

In the original file, pg1.eu_occ, the type for "YearMon" is character. Hence after substring the Year and Month, should they be character type too?

But does it not need to be in numeric format for the MDY function to work?  Can anyone please help on this, thank you so much!

```data eu_occ_total;
set pg1.eu_occ;
Year=substr(YearMon,1,4);
Month=substr(YearMon,6,2);
ReportDate=MDY(Month,1,Year);
Total=sum(Hotel,ShortStay,Camp);
format Hotel ShortStay Camp Total comma17.
ReportDate monyy7.;
keep Country Hotel ShortStay Camp ReportDate Total;
run;```

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Query on Lesson 4 - Computing new Columns - Practice level 2

Hi @mk133201 and welcome to the SAS Support Communities!

You are right. The SUBSTR function returns character values and the MDY function expects numeric arguments. Therefore, these three lines of code are inconsistent:

```    Year=substr(YearMon,1,4);
Month=substr(YearMon,6,2);
ReportDate=MDY(Month,1,Year);```

The code will work nevertheless because of automatic type conversion: Character arguments of the MDY function containing suitable digit strings like "2017" for Year would be converted to the corresponding numeric values. But this is bad practice because it would cause a note in the log saying

```NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
...```

which should be avoided. The same type of note would be written to the log if Year and Month were numeric variables in dataset pg1.eu_occ, as the automatic character-to-numeric conversion would then occur in the two assignment statements.

A clean solution would convert the substrings explicitly by means of the INPUT function, e.g.:

```Year =input(substr(YearMon,1,4),32.);
Month=input(substr(YearMon,6,2),32.);```

(The informat lengths, here: 32, could also be chosen as 4 and 2, resp.)

2 REPLIES 2

## Re: Query on Lesson 4 - Computing new Columns - Practice level 2

Hi @mk133201 and welcome to the SAS Support Communities!

You are right. The SUBSTR function returns character values and the MDY function expects numeric arguments. Therefore, these three lines of code are inconsistent:

```    Year=substr(YearMon,1,4);
Month=substr(YearMon,6,2);
ReportDate=MDY(Month,1,Year);```

The code will work nevertheless because of automatic type conversion: Character arguments of the MDY function containing suitable digit strings like "2017" for Year would be converted to the corresponding numeric values. But this is bad practice because it would cause a note in the log saying

```NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
...```

which should be avoided. The same type of note would be written to the log if Year and Month were numeric variables in dataset pg1.eu_occ, as the automatic character-to-numeric conversion would then occur in the two assignment statements.

A clean solution would convert the substrings explicitly by means of the INPUT function, e.g.:

```Year =input(substr(YearMon,1,4),32.);
Month=input(substr(YearMon,6,2),32.);```

(The informat lengths, here: 32, could also be chosen as 4 and 2, resp.)

Calcite | Level 5

## Re: Query on Lesson 4 - Computing new Columns - Practice level 2

Many thanks for your clear explanation 🙂
Discussion stats
• 2 replies
• 693 views
• 1 like
• 2 in conversation