SAS interview Q & A: PROC SQl and SAS GRAPH and ODS

PROC SQL:

1. What are the types of join avaiable with Proc SQL ?
A.  Different Proc SQL JOINs
JOIN: Return rows when there is at least one match in both tables

•LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

•RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

•FULL JOIN: Return rows when there is a match in one of the tables (source: www.w3schools.com)

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).
The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above. 
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).  source: sql-tutorial.net

Proc SQL INNER JOIN :



Proc SQL LEFT JOIN :




Proc SQL RIGHT JOIN :



Proc SQL FULL JOIN:


Proc SQL UNION Operator:




Proc SQL tutorials:




2. Have you ever used PROC SQL for data summarization?
A. Yes, I have used it for summarization at times…For e.g if I have to calculate the max value of BP for patients 101 102 and 103 then I use the max (bpd) function to get the maximum value and use group by statement to group the patients accordingly.

3. Tell me about your SQL experience?
A. I have used the SAS/ACCESS SQL pass thru facility for connection with external databases and importing tables from them and also Microsoft access and excel files.Besides this, lot of times I have used PROC SQL for joining tables.

4. Once you have had the data read into SAS datasets are you more of a data step programmer or a PROC SQL programmer?
A. It depends on what types of analysis datasets are required for creating tables but I am more of a data step programmer as it gives me more flexibility.For e.g creating a change from baseline data set for blood pressure sometimes I have to retain certain values …use arrays ….or use the first. -and last. variables.

5. What types of programming tasks do you use PROC SQL for versus the data step?
A. Proc SQL is very convenient for performing table joins compared to a data step merge as it does not require the key columns to be sorted prior to join. A data step is more suitable for sequential observation-by-observation processing.PROC SQL can save a great deal of time if u want to filter the variables while selecting or u can modify them …apply format….creating new variables , macrovariables…as well as subsetting the data.PROC SQL offers great flexibility for joining tables.

6. Have u ever used PROC SQL to read in a raw data file?
A. No. I don’t think it can be used.

7. How do you merge data in Proc SQL?
The three types of join are inner join, left join and right join. The inner join option takes the matching values from both the tables by the ON option. The left join selects all the variables from the first table and joins second table to it. The right join selects all the variables of table b first and join the table a to it.

PROC SQL;
CREATE TABLE BOTH ASSELECT A.PATIENT,A.DATE FORMAT=DATE7. AS DATE, A.PULSE,B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM VITALS A
INNER JOIN DOSING BON (A.PATIENT = B.PATIENT)
AND(A.DATE = B.DATE)
ORDER BY PATIENT, DATE;
QUIT;

8. What are the statements in Proc SQl?
Select, From, Where, Group By, Having, Order.

PROC SQL;
CREATE TABLE HIGHBPP2 ASSELECT PATIENT, COUNT (PATIENT) AS N,DATE FORMAT=DATE7., MAX(BPD) AS BPDHIGH
FROM VITALS
WHERE PATIENT IN (101 102 103)
GROUP BY PATIENTHAVING BPD = CALCULATED BPDHIGH
ORDER BY CALCULATED BPDHIGH;
Quit;

9. Why and when do you use Proc SQl?
Proc SQL is very convenient for performing table joins compared to a data step merge as it does not require the key columns to be sorted prior to join. A data step is more suitable for sequential observation-by-observation processing.

PROC SQL can save a great deal of time if u want to filter the variables while selecting or we can modify them, apply format and creating new variables, macrovariables…as well as subsetting the data. PROC SQL offers great flexibility for joining tables.

SAS GRAPH:

1. What type of graphs have you have generated using SAS?
A. I have used Proc GPLOT where I have created change from baseline scatter plots. I have also used Proc LIFETEST to create Kaplan-Meier survival estimates plots for survival analysis to determine which treatment displays better time-to-event distribution.

2. Have you ever used GREPLAY?
A. YES, I have used the PROC GREPLAY point and click interface to integrate 4 graphs in one page. Which were produced by the reg procedure.

3. What is the symbol statement used for?
A. Symbol statement is used for placing symbols in the graphics output.Associated variables can specify the color, font and heights of the symbols displayed.

4. Have you ever used the annotate facility? What is a situation when you have had to use the ANNOTATE facility in the past?
A. Yes, I have used the annotate facility for graphs. I have used the annotate facility to position labels in the Kaplan-Meier survival estimates, where I had to specify the function as ‘label’ and give the x and y co-ordinates and the position where this label is to be placed.

ODS (OUTPUT DELIVERY SYSTEM):
1. What are all the ODS procedure have u encountered?
Tracing and selecting the procedure Output;

ODS Trace on;
Proc steps…;
Run;
ODS Trace off;
ODS Select statement,
Proc steps…;
ODS Select output-object-list;
Run;
ODS Output statement,
ODS output output-object= new SAS dataset;
ODS html body = “path\marinebody.html”Contents = “path\marineTOC.html”Page = “ path\marinepage.html”Frame= “path\marineframe.html”;
…..ODS html close;

ODS rtf file = "filename.rtf" options;
Options like columns=n, bodytitle, SASdate and style.ODS rtf close;


SimilarlyODS Pdf file = "filename.pdf" options;
…….. ODS pdf close;

2. What is your experience with ODS?
A. I have used ODS for creating files output formats RTF HTML and PDF as per the requirement of my manager. HTML files could be posted on the web site for viewing or can also be imported into word processors.
ODS HTML body = ‘path’ Contents= ‘path’Frame = ‘path’ODS HTML close;
ODS RTF FILE = ‘path’;
ODS RTF close;

When we create RTF output we can copy it into word document and edit and resize it like word tables.

3. What does the trace option do?
A. ODS Trace is used to find the names of the particular output objects when several of them are created by some procedure.
ODS TRACE ON;
ODS TRACE OFF;


SAS ODS


ODS, YES! Odious, NO! – An Introduction to the SAS Output Delivery System



Using ODS to Create Customised Output




Controlling SAS output using ODS



The Output Delivery System (ODS) from Scratch




SAS 9 ODS Tip Sheet: