CIBC Interview Questions

[Datura]

1. When you write and run macro codes, what system options do you use?
A: Options source2 mprint mlogic symbolgen

2. What’s the difference between Mprint and Symbolgen?
A: For macro variables, Symbolgen will show the resolved value in LOG. However, Mprint will give both resolved value and converted open codes in LOG.

3. Given a data set like:
CustoNo Score
1 80
1 90
1 70
2 60
2 30
2 40
2 50
3 100
3 170
How to output the 2nd largest value for each by Variable CustoNo?

Proc sort data=A out=B;
By custoNo  descending score;
run;

Data Z;
set B;
by custoNo descending score;
If first.score then cnt=0;
cnt + 1;
if cnt=2 then output;
run;

We can create a counter variable to control the output. Data step is very flexible. Actually, we can use cnt=5/10 or any number to get the 5th, 10th largest numbers.

4. How to write SAS data to Excel?
We have at least 4 methods: Export Wizard, Proc Export, Libname Excel Engine and ODS. We here only demonstrate the Proc Export and Libname Engine approaches.

I. Use Proc Export

proc export data=A  outfile="file path\text.xls"   DBMS=Excel  replace;
run;

II. Use Libname Engine
Libname engine is a new feature of SAS V9, it is very powerful and very easy to use.

 Libname AAA  Excel  "file path\test.xls" ;
 Data AAA.sales;
 set A;
 profit=sales*0.10;
 run;

Pretty good job! However, it is not enough yet. You can continue to compare the pros and cons of each method if you want to really impress them.

Advantage:
We can create new variables by using Libname engine to write out, while Proc Export can not.
Limitation:
If your Excel file contain a spreadsheet with some contents, not blank, then Libname can not overwrite the contents. You have to use Proc Datasets to delete the spreadsheet contents first, then use Libname, it will work.

From good to great, this is the key to win an interview!