Update proc sql code with values from another table

Reply
Contributor
Posts: 27

Update proc sql code with values from another table

Hello,

 

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:

1) name

2) vendor number (which can be associated with one of two variables - variable1 or variable2)

3) province

4) status

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

 

For example

PROC SQL;

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)

QUIT;

 

Any insights is extremely appreciated!

 

Grand Advisor
Posts: 17,313

Re: Update proc sql code with values from another table

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 Smiley Happy 

 

There are blog posts from The SAS Dummy blog on the various methods as well. 

 

Contributor
Posts: 27

Re: Update proc sql code with values from another table

Hi Reeza,

 

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

4) status

6) Year

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 Smiley Tongue ) 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 Smiley Tongue

Thanks!!

Grand Advisor
Posts: 17,313

Re: Update proc sql code with values from another table

Separate the data at the latest step possible. Less room for error this way, in your case before the export. 

Respected Advisor
Posts: 4,606

Re: Update proc sql code with values from another table

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.

PG
Ask a Question
Discussion stats
  • 4 replies
  • 264 views
  • 1 like
  • 3 in conversation