Hi!
I have a dataset of degree programs and am trying to setup a dataset to manipulate into visualizations on Tableau. In order to get the data where I want it, I need to add a state variable and each of the 47 programs need to have all 50 US states listed for it (2,350 rows).
Is there a way to add these in quickly with a SAS program so that I don't have to do this manually in Excel?
Thanks!
Excellent Idea from @Reeza. My solution is the same: Use SQL to create the Cartesian product, but the State table can be fairly easily created from SAShelp.GCstate. GCstate includes territories like GU, PR, VI, etc. and Washington DC ("DC"), but you can screen out the ones you don't want fairly easily.
Code changed as follows:
data Degrees;
drop _:;
DO _I = 1 TO 47;
Degree_name = CATS('Degree', put(_i, 2.));
OUTPUT;
END;
run;
/*DATA States;*/
/* drop _:;*/
/* DO _I = 1 TO 50;*/
/* State_Code = CATS('State', put(_i, 2.));*/
/* OUTPUT;*/
/* END;*/
/*run;*/
PROC SQL;
CREATE TABLE States as
SELECT DISTINCT MapIDNameAbrv as State_Code
FROM SAShelp.GCSTATE
WHERE ISOalpha3 = 'USA';
QUIT;
PROC SQL;
CREATE TABLE Degrees_With_States AS
SELECT Degree_Name
,State_Code
from degrees, states;
QUIT;
Jim
Excellent Idea from @Reeza. My solution is the same: Use SQL to create the Cartesian product, but the State table can be fairly easily created from SAShelp.GCstate. GCstate includes territories like GU, PR, VI, etc. and Washington DC ("DC"), but you can screen out the ones you don't want fairly easily.
Code changed as follows:
data Degrees;
drop _:;
DO _I = 1 TO 47;
Degree_name = CATS('Degree', put(_i, 2.));
OUTPUT;
END;
run;
/*DATA States;*/
/* drop _:;*/
/* DO _I = 1 TO 50;*/
/* State_Code = CATS('State', put(_i, 2.));*/
/* OUTPUT;*/
/* END;*/
/*run;*/
PROC SQL;
CREATE TABLE States as
SELECT DISTINCT MapIDNameAbrv as State_Code
FROM SAShelp.GCSTATE
WHERE ISOalpha3 = 'USA';
QUIT;
PROC SQL;
CREATE TABLE Degrees_With_States AS
SELECT Degree_Name
,State_Code
from degrees, states;
QUIT;
Jim
So, you have a dataset containing information on 47 degree programs. You need to add all 50 states to each degree.
I think just taking the Cartesian product via SQL should do it pretty simply and easily.
Something like this, although you'd have to use real state codes or state names and real degree names. I just used dummy names, but the SQL should be essentially the same.
Jim
data Degrees;
drop _:;
DO _I = 1 TO 47;
Degree_name = CATS('Degree', put(_i, 2.));
OUTPUT;
END;
run;
DATA States;
drop _:;
DO _I = 1 TO 50;
State_Code = CATS('State', put(_i, 2.));
OUTPUT;
END;
run;
PROC SQL;
CREATE TABLE Degrees_With_States AS
SELECT Degree_Name
,State_Code
from degrees, states;
QUIT;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.