Here are a set of SAS Macros to read data from MS Excel using DDE…
Let me explain with some scenarios with the Excel data stored in “C:\Names.xlsx” in worksheet named “Sheet1″…
This excel file has in fact data that can be read into 2 datasets…i.e. Name / State in one dataset and City/State into another dataset right…We can accomplish that using that using the SAS macros below….
The Macro Call OpenExcelWorkbook opens the excel work book for copy…
How many a times at work have you gone thru the logs looking for ERROR, WARNINGS, NOTES etc for more information about the generated problem?…I’ve been doing a lot like that lately for developing / debugging / lines of code for more information…I just wished I had a piece of code that would search and report me the location / line number and few more information about….luckily I found this macro in a SAS SUGI paper…here’s the link….www2.sas.com/proceedings/sugi25/25/po/25p219.pdf
The %EVAL function evaluates integer arithmetic or logical expressions. %EVAL operates by converting its argument from a character value to a numeric or logical expression. Then, it performs the evaluation. Finally, %EVAL converts the result back to a character value and returns that value.
Operands that contain a period character cause an error when they are part of an integer arithmetic expression. The following examples show correct and incorrect usage, respectively:
Data analysis is a process of inspecting, cleaning, transforming, and modelling data with the goal of highlighting useful information, suggesting conclusions, and supporting decision making.
The first step in this process is to know about your data…
– Know what kind of data are you dealing with i.e. financial, pharmaceutical etc.
– If you have access to the data..quickly run a freq / means to get data-level information.
– Depending upon your business specific questions we might have to slice and dice / summarize the data.
Often we need to add a prefix to some or all variables in a dataset before we might have to merge datasets that have similar column attributes…This macro would allow you to do that….
SAS Macros that Convert a Directory of Transport Files @ http://www.sas.com/govedu/fda/macro.html
When you submit code or a catalog entry from the Enhanced Editor, the filename or catalog entry name and their respective folders are placed in these environment variables:
contains the full path of the submitted program or catalog entry. The full path includes the folder and the filename.
contains only the name of the submitted program or the catalog entry name.
You can then extract the filename and full path for use in your SAS programs.
After the following DATA step runs and the data is sorted, the PRINT procedure includes the filename in the title and the full path in the footnote of the procedure output. The results are shown below as a Screen Shot
Here’s a simple SAS Macro to delete a SAS dataset…It takes the name of the SAS dataset as the parameter…
This macro splits a dataset to multiple datasets vertically with a common primary key. For eg, a dataset has 400 fields and 20,000 records. If we can split the dataset into two, with 200 fields and 20,000 records in each dataset with a common field like loan number as primary key would be helpful to load the details for analysis.
Here is a macro to quickly cleanup the work directory programmatically… It uses Proc Datasets with KILL option to delete the datasets in the library… The valid values for the parameter memtype are ACCESS ALL CATALOG DATA FDB MDDB PROGRAM VIEW only…