I have a reference table that displays how my industries are grouped into sectors , and what has to go in each of the charts. The reference table looks as such:
Cluster | Sector | Industry1 | Code1 | industry2 | code2 | Table_num_per_sector | chart_title |
RW | CP | Pharmacy Sales | 6124 | 1 | test1 | ||
A | AG | Ag nfd | 010 | 4 | test8 | ||
A | AG | Mush | 012 | 4 | test8 | ||
A | AG | Fruit | 013 | 4 | test8 |
I want to reference this table and output csv files. The data set I will perform the analysis on is this:
code | year | industry | emp |
010 | 2008 | Ag nfd | 1 |
010 | 2009 | Ag nfd | 2 |
4 | 2008 | B | 2 |
4 | 2009 | B | 2 |
012 | 2008 | Mush | 1 |
012 | 2009 | Mush | 2 |
013 | 2009 | Fruit | 3 |
6124 | 2008 | Pharmacy Sales | 2 |
6124 | 2009 | Pharmacy Sales | 4 |
I want to use the reference table to go through this data set and output csv files like this (for example).
CSV1: Called test1
year | Pharmacy Sales |
2008 | 2 |
2009 | 4 |
CSV2: called test8
year | Ag nfd | Mush | Fruit |
2008 | 1 | 1 | 0 |
2009 | 2 | 2 | 3 |
It is important to note how the outputed csv's are called what they are told to from the reference table, and that missing values such as 2009 for Fruit are given a value of 0. Code1,Code 2 and industry1,industry2 in the reference table are to be compared with code and industry in the dataset that will be analysed.
I am wandering how to go about doing this. As I am working on a data set similar to this but on a bigger scale.
You might need to supply a little more detail on exactly HOW your reference table is used. Since the "analysis" you show is different then that tells me that the rows of the reference are used differently. Without a clue as to what may be wanted how to use your reference table is not obvious.
Call execute can be used with a data set to conditionally generate code, if you know which code you want. If you do not know which code you want then that is an entirely different set of questions.
We would need to know the purpose and possibly more values for each of the variables in the reference data set and then the type of "analysis" for each csv, especially if it is anything more than selecting records from the data set.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.