I have an Excel sheet with a few dozen columns. One of these columns is called "distribution options". In this column you find general keywords to indicate how it will be distributed. Below you will find all the available "distribution options".     Fast Track Heir Fast Track Living Expenses Fast Track Balance Fast Track Surety Manual Distribution Office Distribution Notery Distribution Standard   I wanted to read out the entire column and receive a table how many times each of these distribution options appear in the column.   The result in the end should look similar to this.   Distribution Options Count Fast Track Heir 102 Fast Track Living Expenses 230 Fast Track Balance 17 Fast Track Surety 43 Manual Distribution Office 2 Distribution Notery 18 Distribution Standard 43   I wrote a script, but for some reason it doesn't count the Fast Track Heir, Fast Track Surety and Manual Distribution Office. All the other values (the same as above) are correct.   I used this script, but perhaps there is an easier approach. I simply can't figure out why Heir, Surety and Manual return "0" values, whereas the rest returns the values shown above. There are no errors in the log either. This time I checked the data myself and used COUNTIF(range;"word") to count it in Excel. The values above are correct. The script somehow isn't.    PROC SORT IN=atropos.dossiers OUT=atropos.dos_opt; BY dossier_manager; RUN; DATA atropos.dos_rap; SET atropos.dos_opt; BY dossier_manager; RETAIN Heir Living_Expenses Balance Surety Manual Notary Standard; IF first.dossier_manager THEN DO; Heir=0; Living_Expenses=0; Balance=0; Surety=0; Manual=0; Notary=0; Standard=0; END; SELECT (distributionoption); WHEN ('Fast Track 1 Heir') Heir +1; WHEN ('Fast Track Living Expenses') Living_Expenses +1; WHEN ('Fast Track Balance') Balance +1; WHEN ('Fast Track Surety") Surety +1; WHEN ('Manual Distributionassignment Office') Manual +1; WHEN ('Distribution Notery') Notery +1; WHEN ('Distribution Standard') Standard +1; OTHERWISE; END; IF last.dossier_manager THEN OUTPUT; KEEP dossier_manager Heir Living_Expenses Balance Surety Manual Notary Standard; RUN; ... View more

I want to select all our documents between a date range.   I import a CSV file: (I already changed the dates in the CSV file to the ddMMMyyyy format, i.e. 6APR2016.) PROC IMPORT DATAFILE="O:\...\documents.csv" DBMS=csv replace OUT=atropos.ave_docs; GETNAMES=yes; DATAROW=2; DELIMITER=';'; RUN;   Next up I start making a query: PROC SQL; CREATE TABLE atropos.ave_rap AS SELECT document_handler, document_state, COUNT(*) AS amount_of_documents, MIN(r_creation_date) AS oldest_document FORMAT=DATE9. FROM atropos.ave_docs WHERE document_state IN ('HOLD','TODO') AND kind_of_document LIKE '%AVE%' AND r_creation_date BETWEEN '29FEB2016'd AND '01APR2016'd GROUP BY document_handler, document_state; QUIT;   This is the log: 1152 PROC SQL; 1153 CREATE TABLE atropos.ave_rap AS 1154 SELECT document_handler, document_state, COUNT(*) AS amount_of_documents, 1154! MIN(r_creation_date) AS oldest_document FORMAT=DDMMYY10. 1155 FROM atropos.ave_docs 1156 WHERE document_state IN ('HOLD','TODO') 1157 AND kind_of_document LIKE '%AVE%' 1158 AND r_creation_date BETWEEN '29FEB2016'd AND '01APR2016'd 1159 GROUP BY document_handler, document_state; NOTE: Table ATROPOS.AVE_RAP created, with 9 rows and 4 columns. 1160 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.13 seconds cpu time 0.12 seconds   Knowing that the CSV-file has 696.911 rows, it would be very unlikely to only receive 9 rows of data after the query.   I need all the documents created in March 2016. In other words the r_creation_date has to be greater than 29FEB2016 and smaller than 01APR2016. I have tried different approaches, like BETWEEN, DATE, FORMAT, %LET, SET,... I even changed all my dates to the default SAS format ddMMMyyyy and it still doesn't work. I can't figure this one out, simply because there is no error in the log. Do I need to define the format of the column in the import? Any ideas? ... View more

When an employee opens a dossier, a few tasks are created automatically. These tasks receive a sequence number (1 to 5) indicating in which order they need to be performed. Only one task can be active while the others are waiting. When a task is completed it becomes done, unless they cancel it. Each task has a start date, a close date and a deadline depending on each task.   I received an Excel file which contains task_handler (employee), object_name (title of the task), start_date, close_date, deadline, task_state and sequence.   object_name deadline start_date close_date task_handler task_state sequence Intake 8/03/2016 1/03/2016 7/03/2016 Handler A DONE 1 Intake 8/03/2016 1/03/2016 7/03/2016 Handler A DONE 1 Intake 8/03/2016 1/03/2016 22/03/2016 Handler B DONE 1 Intake 8/03/2016 1/03/2016 4/03/2016 Handler C DONE 1 Intake 8/03/2016 1/03/2016 9/03/2016 Handler D DONE 1 Intake 8/03/2016 1/03/2016 22/03/2016 Handler E DONE 1 Intake 8/03/2016 1/03/2016   Handler F ACTIVE 1 Intake 8/03/2016 1/03/2016   Handler G ACTIVE 1 Intake 8/03/2016 1/03/2016   Handler H ACTIVE 1   I have to make a table in which I can show how many "intakes" an employee has closed and how many "intakes" an employee still has opened. In addition, they also want me to display how many of these tasks were closed after the deadline expired. (Obviously this part requires me to focus on task_state ACTIVE and DONE.) I also have to add a final column that displays how many tasks have been CANCELLED.   Important note: Employees are able to adjust the sequence of tasks, which means that I cannot reply on sequence value 1. Instead I use "like '%intake%'". If the task is cancelled, the deadline, the start_date and the close_date are empty cells. If the task is still active, the close_date is an empty cell.   The result should look like this:   task_handler task_state amount_of_tasks expired Handler A ACTIVE 4 0 Handler A DONE 5 1 Handler A CANCELLED 1 0 Handler B ACTIVE 3 0 Handler B DONE 9 5 Handler B CANCELLED 0 0     This is the query I created so far:   (Import Excel file into SAS library.) PROC IMPORT OUT= ATROPOS.Tasks DATAFILE= "random_location" DBMS=XLS REPLACE; GETNAMES=YES; DATAROW=2; GUESSINGROWS=696430; RUN;   (Add a column called Expired, which contains close_date - deadline.) PROC SQL; CREATE TABLE Atropos.TasksEXP AS SELECT *, (close_date - deadline) AS Expired FROM ATROPOS.Tasks; QUIT;   (Creating the table and adjusting Expired values. (Expired > 0 = Yes // Expired <= 0 = No)) PROC SQL; CREATE TABLE ATROPOS.Startup AS SELECT task_handler, task_state, COUNT(*) AS amount_of_tasks, Expired FROM ATROPOS.Tasks WHERE task_state IN ('ACTIVE','DONE','CANCELLED') AND object_name LIKE '%Intake%' AND IF (Expired > 0) THEN (Expired = 'Yes') ELSE (Expired = 'No') GROUP BY task_handler, task_state; QUIT;   I am having issues getting the IF THEN ELSE statement to work. IF remains unrecognized in a black font, whereas THEN and ELSE are recognized in a blue font. I have tried different notations, even without using brackets, but nothing appears to be working. Secondly, I find it difficult to adjust the values in Expired, even if I were to use a data or update statement. Any ideas? I am guessing I am using a very devious method or was I on the right track here?   Thanks in advance.       ... View more

I just started working as an analyst within a bank. Since they work in databases on different platforms and since the leading analyst has a huge workload, I am tasked with exploring SAS and its possibilities.   One of these databases is a java webbased database build in a program called QIT. This database contains every dossier that has been opened for our customers. This database contains information such as:   - kind_of_document   (AVE, DOK, FAK, TRIGGER,...) - document_handler   (Name of the employee of the bank who is handling this dossier.) - r_creation_date   (The date on which the dossier has been created.) - document_state   (CANCELED, HOLD, TODO, TREATED)   The exercise is to create a table in SAS that checks all AVE related dossiers with a document_state of TODO or HOLD, sorted by document_handler. (See results below.) Since I have no access to the QIT database yet, they have given me an Excel file containing said information for the month February. At the end of my exercise, I can easily double-check my results through a similar query in QIT. I noticed that my SAS query yields 45 rows as a result, whereas my QIT query yields 67 rows as a result. I have to be doing something wrong.     QIT query: select document_handler, document_state, count (*) as amount_of_documents, min (r_creation_Date) as oldest_document from atp_document where document_state in ('TODO', 'HOLD') and kind_of_document like '%AVE%' and r_creation_date < date ('01/03/2016') and r_creation_date > date ('31/01/2016') group by document_handler, document_state     SAS query: PROC SQL; CREATE TABLE Atropos.Open_Doc AS SELECT document_handler, document_state, COUNT(*) AS number_of_documents, MIN(r_creation_date) AS oldest_document FORMAT=DATE7. FROM Atropos.Documents (=imported Excel file containing the information for the month February) WHERE document_state IN ('TODO','HOLD') AND kind_of_document LIKE '%AVE%' GROUP BY document_handler, document_state; QUIT;     Result: Comparing both tables with each other tells me a few things.   - I am missing 22 document_handlers in my SAS table - The number_of_documents isn't always correct for every document_handler (for example, my query yields 2 documents for handler A and in QIT my query yields 4 documents for handler A     This is the result from QIT and this is CORRECT! (First 4 rows only.)   document_handler document_state amount_of_documents oldest_document Handler A HOLD 1.0 23/02/2016 10:14:31 Handler B HOLD 4.0 19/02/2016 16:39:50 Handler C TODO 2.0 24/02/2016 11:12:46 Handler C HOLD 4.0 2/02/2016 15:43:21     This is the result from SAS and this is WRONG! (First 4 rows only.)   document_handler document_state amount_of_documents oldest_document Handler B HOLD 2.0 19/02/2016 Handler C TODO 1.0 29/02/2016 Handler D HOLD 2.0 19/11/2016 Handler E HOLD 2.0 06/02/2016   I think something is wrong with the COUNT command, but I cannot seem to put my finger on it. Any help is greatly appreciated. ... View more