BookmarkSubscribeRSS Feed
camfarrell25
Quartz | Level 8

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!

 

4 REPLIES 4
Reeza
Super User

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. 

 

camfarrell25
Quartz | Level 8

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 😛 ) 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 😛

Thanks!!

Reeza
Super User

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

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 690 views
  • 1 like
  • 3 in conversation