08-30-2016 09:55 PM
I'm currently attempting to create a code that would pull values from another table (further referred to as the tracking table which isin excel format) to customize the code.
For example, I have a list of vendors with unique ID and conditional on that I have the following variable:
2) vendor number (which can be associated with one of two variables - variable1 or variable2)
I have a massive dataset (datasethave) that contains information for a bunch of vendors and I wish to have a code that is customized dependent on the tracking table which will pull data from the massive dataset and create a table individual to each vendor. Therefore, for any given vendor in that tracking table, the code will be changed such that a new dataset is created specific to each vendor. The essential idea behind this is to avoid having to code each vendor.
The values/variables in bold are determined by the tracking table
CREATE TABLE work.vendorreport(lender name) AS
SELECT (variables for report) FROM work.datasethave
WHERE (variable 1 or 2) = (vendor number) and province = (province code)
Any insights is extremely appreciated!
08-30-2016 10:26 PM
Create SAS dataset from vendor list.
Change where condition to:
Where province = province and vendor in (select vendor from vendor_list);
You don't say what you want to further do with the table. That's the key info. For example if you're creating an excel file or PDF or word report there are ways to automate that using a BY statement. Creating a SAS dataset for each group value is not considered best practice.
Otherwise, if you insist on a dataset for each vendor search on here regarding creating subsets of data dynamically. The question pops up weekly
There are blog posts from The SAS Dummy blog on the various methods as well.
08-30-2016 10:54 PM
Thank you for taking the time to reply to my post.
I wish to create individual reports for each vendor - although I suppose for each vendor there is also a peer group that consits of other vendors.
My plan was to:
1) create a variable called "Vendor" which would identify the vendor I wish to report for
2) second variable called "Group" which would identify if I'm referring to the Vendor targetted by the report or the peer group (sum of other vendors, as identified by my tracking table)
3) Province for each
7) statistical summaries based on that selection such that for each report I have a statistical summary (i.e. sum of sales) for that lender, it's peer, and also by province, year and status of the vendor. Such that I will have an aggregated table each individual "observation" will consist of a unique combination of variables "Vendor", "Group", "Province", "Status", "Year"
That aggregated table I wish to export to Excel in the form of a pivot table using a connection string which will then generate a different report based on my choice of a particular vendor and criterias (statistical data by year, province, status) and comparison with Peers and will update standardized graphs.
Therefore, the reason behind my choice of creating a distinct dataset for each vendor (apart from my simple lack of experience ) is to create those aggregate summaries individually by vendor before "adding" them all up in a big table which I will be able to simply select my filter in a pivot table and all my graphs will update dependent on my choice (if that makes sense)
Sorry for the confusion - I feel like I'm confusing myself
08-30-2016 11:44 PM
Your data should reside in a single database and be indexed. Extracting records for a given vendor would be simpler and more efficient than fetching a vendor specific dataset. But more importantly, data maintenance would be a lot easier.