Hi Sharon,

Why do we need that IF statement after the loop? Since the loop works until years=10 or Capital>..., I don't understand how that IF could ever be true (and, therefore, needed).

Thanks!

Luca

Maybe to add a point:
The thing I didnt see was, that the output statment has the same effect as the if statement you discussed. So if you use the output statement you have the year=10 even without using the if statement.

If you want to use OUTPUT, you may use the following code (Please note I comment out the IF statement). But this will print out all 10 years' values instead of the last/final year's value. The result is pasted as well. Hope this helps!

data invest;


do year=1 to 10 until(Capital>=50000);

      capital+2000;

      capital+capital*.10;

    output;


end;


*if year=11 then year=10;


run;


													

A SAS BASE certification course for absolute SAS Beginners Lecture - Coding Exercise 2: Solution

This is a multiple dimensional array program. My explanations are in BOLD in the following program.

data sale2;

set sale1;

array sale (2,3) saleCA1 -- saleTX3;

/* (2,3) will separate the variables into the following pattern:

2 -- rows -- each row represent one state

3 -- columns --- each column represent one sale, such as column 1 represents sale 1, column 2 represents sale 2, column 3 represents sale 3

saleCA1 saleCA2 saleCA3

saleTX1 saleTX2 saleTX3 */

array state_sale (2) state_sale_CA state_sale_TX;

/*using this array to create 2 new variables state_sale_CA state_sale_TX, which will contain the total sale for that year for that state */

do i = 1 to 2;

/* This is the outer DO loop: i = 1 for state CA; i = 2 for state TX*/

state_sale (i) = 0;

/*set the initial total state sale to 0 so when a new iteration / new state occurs, the initial total state sale is 0*/

do j=1 to 3;

/*This is the inner DO LOOP, when i = 1 for state CA; state_sale (1) = 0; */

state_sale{i}+sale{i,j};

/* this is the same as state_sale(i) = state_sale{i}+sale{i,j};

so when j = 1: state_sale(1) = state_sale{1}+sale{1,1}= 0 + saleCA1

when j = 2: state_sale(1) = state_sale{1}+sale{1,1} + sale (1,2)= 0 + saleCA1+saleCA2

when j = 3: state_sale(1) = state_sale{1}+sale{1,1} + sale (1,2) + sale(1,3)= 0 + saleCA1+saleCA2+saleCA3

After the iteration when j = 3, the calculation/add up for CA total sale is done, and this is for the outer loop i = 1*/

/*Then when i = 2 this is for TX, same process goes on, the TX total sale will be added up in same process*/

end;

end;

run;

Hope this helps! Lecture 62 is on multiple dimensional array which include the detailed explanation on a similar example.

Regards,

Sharon

A SAS BASE certification course for absolute SAS Beginners Lecture - SAS functions: overview

Updated a month ago

SAS functions

In the video, there were repeated columns for gender, total score and average score (without function and the other with a function). I know that there were repeated because of the functions. However, is it possible to include the functions at the beginning statements without writing a separate code?

For example:

DATA scoredata2;

set scoredata0;

TotalScore_func = sum (score1, score2, score3);

AverageScore_func = mean (score1, score2, score3);

run;

Is this possible?

How can gender only have one column with capital letter?

Thank you.

sharon — Instructor

· a month ago
Posted a month ago

Yes, you don't have to include the following codes -- they are just for comparison.

TotalScore = score1 + score2 + score3; /*missing values*/


AverageScore = TotalScore/3;/*missing values*/


																		

As for gender, the original data is gender = 'm' or 'f', I used UPCASE() function to convert the letters to capital letters in the following code.

Gender_func = UPCASE(gender);

Hope this helps!

Regards,

Sharon

---

---

The ultimate SAS Advanced certification course

Yi

Omiting 'calculated'

Hi Sharon,

Here we use claculated column to sort dataset, but don't include the word 'calculated' in front. Why does this work? When can we omit the 'calculated'?

sharon — Instructor

· a month ago
Updated a month ago

Good question!

I double-checked the most updated SAS 9.4 SQL procedure user's guide, for Order By, you can use an alias to refer to a calculated column.

I also tried adding the 'calculated' in front of the variable score_ave in Order By statement, the result and log are same as the code without the 'calculated'. So I think you don't have to use the 'calculated' for calculated column in Order By, but you can use it as it is easier to remember for the sake of consistence.

proc sql;

select *, sum (score1, score2, score3 )/3 as score_ave

from score_data

order by calculated score_ave;

quit;

Hope this helps!

Regards,

Sharon

---

The ultimate SAS Advanced certification course

Ines

Updated 2 months ago

Regarding Lecture 14

Hello Sharon, In the following code, I noticed that when we add the "order by gender" clause after the "group by" clause, this messes up with the aggregation output for the column class (6th column). Hence, the table is only aggregated by gender and not anymore by class.

I have also noticed similar results when we add and order by clause with respect to class or name:

proc sql;

select *, mean (score1, score2, score3 ) as score_mean,

max (calculated score_mean) as max_mean, min(calculated score_mean)as min_mean

from score_data_class

where calculated score_mean is not missing

group by gender, class

order by gender desc;

quit;

Two questions:

1. Would you know how to keep the aggregation by class valid when we add the order by clause?

2. Would you know how to aggregate by both gender+class and then order the names alphabetically within each category (gender+class)?

Thank you

sharon — Instructor

· 2 months ago
Updated 2 months ago

Good questions!

1. order by gender desc;

This code only sorts/orders by Gender, not sorts by class, therefore within each gender group/value, class appears as un-sorted/un-ordered. You may use the following code to order both Gender and Class

order by gender desc, Class;

Below is the result:

2. Again, you only need to change the order by clause like below, you don't have to include the DESC option for Gender

proc sql;

select *, mean (score1, score2, score3 ) as score_mean,

max (calculated score_mean) as max_mean, min(calculated score_mean)as min_mean

from score_data_class

where calculated score_mean is not missing

group by gender, class

order by gender desc, class, name;

quit;

Hope this helps!

As always, I appreciate fair and honest review/rating.

Regards,

Sharon

Mark as top answer

Ines

· 2 months ago
Posted 2 months ago

Thanks a lot Sharon. This exactly answers my questions! Regards, Ines

Mark as helpful Mark as top answer

sharon — Instructor

· 2 months ago
Posted 2 months ago

You are welcome!

---

The ultimate SAS Advanced certification course

Sean

Updated 2 months ago

Looking at Practical examples. 3. Computing Percentages within Subtotals - Can you please tell me why the percentage needs to be formatted as 8.2. Why doesn't 6.2 display the value properly?

calculated Count/Subtotal as Percent format=percent8.2

Also is there a easy way to remember when you need to put the full stop after a format? i.e. above there is no full stop after the w.d

sharon — Instructor

· 2 months ago
Posted 2 months ago

Here is the info on Percent format, the BOLD part is the answer, that's why 6.2 only gives us 67% instead of 66.67%, I do want the values with 2 decimals.

PERCENTNw.d

Syntax Description

w

specifies the width of the output field.

Default:6

Range:4-32

Tip:The width of the output field must account for the minus sign ( - ), the percent sign ( % ), and a trailing blank, whether the number is negative or positive.

dspecifies the number of digits to the right of the decimal point in the numeric value. This argument is optional.

Range:0-31

Requirement:must be less than w

Hope this helps!

Regards,

Sharon

---